Video instructions and help with filling out and completing Where Form 8815 Dependents

Instructions and Help about Where Form 8815 Dependents

Music one of the challenges of working with list is having duplication of information and when I say duplication what I'm actually talking about is different variations of the same information so for example in this cell here we can see backed up toilet and toilet back dump mean the same thing but there's different variations of the way they're spelt now this affects how we do our filtering and our searching and other things so what I'm going to show you today is how we can create a unique list of items that we can use on not only the dropdowns in our spreadsheet but also in the choice list of our Google Forms the best part is as people are adding new items to either the spreadsheet or in the Google form it will automatically update our master list and so we can get updated list automatically generated as new information gets entered in so let's start and take a look at what we have going on here so in this column we've got all this different problems that are coming in for people who having problems with something going on in the suite this is for a rental rental units and people have entered in this information now backed up toilet and toilet backed up for the same thing just for the sake of simplicity I'm going to copy this information here and paste it here just we have that exact same thing now here's my self generating list notice we all have duplication of fan broken we have the backed up toilet those things are all duplicated so what we want to do is create a unique list of all those items so what we're going to do is use the front unique function which is equal to unique and then give it the range that you want it to check out so I'm just going to click and drag down to the cell here and close it off notice now that I only see one of every single piece in other words a unique item now I'm going to take this information right now it's kind of not in alphabetic order so I'm going to put in alphabetical order by using the sort function so first I'm going to take this information here highlight it and just cut it so I have in memory still we'll start off with a sort sort says what's the range you want well that's the formula we just had a second ago so we'll put that back in and then we follow it with a comma and what column do you want to sort it by I'm going to sort by the first one and you want it ascending I'm going to put it true so now I get that same list but now it's in a alphabetical order what I'm going to do now is enter in a new row with a new item so here Tim has entered in that you've got a broken window look what's happened up here we have broken window now showing up in our list so anytime something new gets entered into column C it's automatically going to get entered into that self-generating list if it's something that already exists it's not going to repeat it it's just going to have one unique listing of it so let's create a drop-down menu of this list here so I'm just going to click on seed to highlight the whole column let me go to data here and we're going to go data validation now here's a quick trick for everyone we don't need the first item here which is the label to be included in that drop-down so just go into there and you can see the formula C colon C which means start at seeing all the way to the end of C I'm going to say start at C - and go to the very bottom of it now a lot of people when they do these lists what they'll do is they'll select a list of items and they'll enter those items in there but what we're going to do is say choose from a range or number of cells so we're going to click here what's the data we want well I'm just going to click and run and drag over this piece here and click OK so I'm saying anything that shows up in there that's what is going to be my drop-down and I put it save notice what's happening here now I have all those different choices here now available to me so that's how we get the spreadsheet working now let's take a look at our form here in the form I have just a quick simple pieces a name and the problem the problem is going to be the different items and what I want to do is have this list here pulling from this list here to do that we're going to use an add-on called form Ranger I'm assuming you know how to install form Ranger it's very easy if not you can google it but what form Ranger does is say hey I'm going to take the contents of a right cheats and I am going to bring it over and put it into a multiple-choice listing of questions so it's got to be something that has you know either checkboxes or a list or a or the bullets here radio box radio buttons so I'm going to populate this or this is a question problem and I populate my list I'm going to create a brand new one here find the spreadsheet that were interested in find the sheet that we want so in this case I want to form responses 1 and my column header if you remember is the self generating list and there's all my items in there I'll hit next what's the name of this