Hello to all!



As you can see on the attached Excel file I created a data validating drop down list (B2 -B5) and cannot figure out how to modify the list of the cells in the drop down list so that when I choose an item from the drop down list, not just the current cell but the two cells next right to it would also get updated.

For example, if I choose John from the drop down list, I'd like his building and apartment number also pop in once I let the mouse button go on John:

John A 1

So, maybe I should ask the question: How can you create a drop down list of not one column but two or three columns of lists?

Why I need this feature on the first place instead of merging the building and apartment number info in the first cell? So, that later I could do data sorting based on the building name or apartment numbers.

Attached Files

Data_Validation.xls (13.5 KB, 20 views)

will this work for you

Attached Files

Data_Validation(1).xls (22.0 KB, 25 views)

Wow Grizz!

No wonder, why I was clueless about how to do it. I will have to spend time on analyzing your elegant solution. But until then, would you please, explain to me just one thing:

According to Excel help, the syntax of the function Index can be either 3 or 4 items in a bracket but your solution contains only two:

a range and a Match expression. How about that?

It's true, that the Match expression contains 3 items but I am assuming that the result of the Match expression is only one item, so that's why I am puzzled a bit here.

As I said, I will definitely have to learn how to use the Index and Match (one at a time), but without your answer, I could not figure out the mismatch in the required number of items in your Index expression.

I strongly recommend reading this article on the INDEX function, just to remind that INDEX does in fact NOT return a cell value but a reference....

__________________
Using O365 v2407 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Grizz, Thank you for your solution, I tried to make it work in my problem but I couldn't make it work.

The only difference between the sample spreadsheet that I uploaded here and the one that I need the solution for is that the "look up" table is located on a different sheet.

When I created the sample spreadsheet, I put the look up table on the same sheet where the main information is located just to keep it simple but I guess, my problem requires a different solution when the look up table is on a different sheet.

Am I right, or I am just missing out on something?

Pecoflyer: thank you for the link to that page, indeed it is a great page about the Index function.

formula will work you just need to add the file path

Attached Files

Data_Validation%281%29(2).xls (22.5 KB, 18 views)

If you notice if your drop down is blank you will get a #N/A displayed
C & D if you do not want that to appear check out the modified work sheet

Attached Files

Copy of Data_Validation%25281%2529%282%29(3).xls (24.5 KB, 13 views)

I took the liberty of adding an array solution

Attached Files

Data_Validation_Array.xls (38.5 KB, 18 views)

__________________
Using O365 v2407 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

grizz:

Thank you for the modifications! Where I made the mistake was, that when I created a list, I highlighted the whole table instead of just the first columns.

You also helped me with the "#N/A" annoyance in unused cells - without asking for it -, indeed it made the whole page look full of errors, I just thought, I'll have to live with it.

Speaking of annoyances, there are two more, that it would be nice to correct.

One is that if I enter a word in the 1st column that doesn't exist in the look up table , I get ### in the 2nd column and #N/A in the 3rd one. How can I change the code, so that it will ignore such words in the 1st column and as a result leave the 2nd and 3rd column cells empty?

The other annoyance is that some of the items in the 1st column of the look up table does not have any data in its corresponding 2nd and 3rd column, as a result if I choose this item on the spreadsheet, the result is a zero in the 2nd and 3rd column of the spreadsheet. It would be nice to have the code not adding zeros to these columns in such instances.

I also have question about the IF condition of the code, that I couldn't found an answer on by searching through Google:

=IF($D97="";"";INDEX(...

What is the meaning of the expression of semicolon between double quotes?

My guess is, that it means "If $D97 equals to any item of the list (or anything except nothing)

...so how does one come up with the underlined code?

Pecoflyer:

Thank you for the array version, it works. Would you please, explain to me what is the advantage of doing this Excel coding with an array? (So, that I will know which one to use in the future.)

grizz and Pecoflyer,

Thank you for your pointers and solution!

Perhaps this link will give you some insight on what array formulas can do.
They do tend to slow things down, though

__________________
Using O365 v2407 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post