Autofill
I have one column that has a dropdown of customer names. I want the "Address" cell to autofill with the address that was picked in the previous cell. I tried to do an automation but it seems to change the whole Address column instead of the cell. I have done an IF statement but it is only limited to 2 options... IF X, then Xaddress, ELSE Yaddress.
Best Answer
-
You can change the value of a cell using an automation but the thing you change it to is static. I think the sheet of addresses is a better option.
As for your other question...
INDEX MATCH is great to match against one thing, such as a name. You have a name in both sheets and use that match to identify the correct row to return.
INDEX COLLECT is to look at a collection of things, for example to return the address if the name is actually forename and surname in two columns and you need both to identify the correct row. Match will not work with multiple criteria. You can also use INDEX COLLECT to return the nth match - not just the first row that meets the criteria.
VLOOKUP is less flexible than INDEX MATCH/COLLECT and I rarely use it. The thing to lookup needs to be on the left and once created your can't add or remove any columns between the column you are looking in and the column you are returning. It is useful to quickly return multiple columns based on one look up value as you can create a VLOOKUP in one column and drag it to others, iterating the column to return without having to set up multiple cross-sheet references for the column to return.
Hope that helps.
Answers
-
Try an INDEX and MATCH. You should be able to index the address column and match on the customer name.
Have a look at the Index Match section here:
-
This wasn't really what I was looking for but I also realized that what I was trying to do wasn't very realistic. I ended up making a sheet that contains all of the addresses, and then doing what you suggested. I was somehow trying to get Smartsheet to change the value of a cell.
One thing I'm still a little unclear on is the specific when to use VLOOKUP, INDEX(MATCH()), or INDEX(COLLECT()).
-
You can change the value of a cell using an automation but the thing you change it to is static. I think the sheet of addresses is a better option.
As for your other question...
INDEX MATCH is great to match against one thing, such as a name. You have a name in both sheets and use that match to identify the correct row to return.
INDEX COLLECT is to look at a collection of things, for example to return the address if the name is actually forename and surname in two columns and you need both to identify the correct row. Match will not work with multiple criteria. You can also use INDEX COLLECT to return the nth match - not just the first row that meets the criteria.
VLOOKUP is less flexible than INDEX MATCH/COLLECT and I rarely use it. The thing to lookup needs to be on the left and once created your can't add or remove any columns between the column you are looking in and the column you are returning. It is useful to quickly return multiple columns based on one look up value as you can create a VLOOKUP in one column and drag it to others, iterating the column to return without having to set up multiple cross-sheet references for the column to return.
Hope that helps.
