Lookup for a cell based on value of a different cell

[jonhall]

Hi,

I have a spreadsheet in which the value of cells in Column B are selected from a drop down box (created through a Data Validation rule) from the values entered in Column M.

I'd like for the values in Column C to come from a drop down box with the relevant entries from the cells to the right of the relevant cell in Column M.

Ie: If the value of B10 is set as what's entered in M1, I'd like the options for C10 to be N1, O1, P1, etc.

Is this possible? And if so, would someone be so kind as to advise me how to do it.

Thanks, in advance for your help.

Jon

 

[David20]

If I understand your question, you want to be able to select a value from a drop-down list in B and then have the neighboring cell, C, create a drop-down based on B. Correct?

If that's what your looking for, try an INDIRECT function and a set of Named Ranges.

For each item in Column M, make the cells to the right a Named Range with the same name as that item. For example, if M1 = "First" and it has three relevant entries in N1:P1, then name N1:P1 "First". (I like to make the Named Range refer to extra empty cells so I can add items without having to remember to changed the Range.)

Next, in C1, put the formula "=INDIRECT($B$1)" in the Data Validation "Source" box. The "Allow" box should show "List". When you click OK on the Data Validation form it will probably warn you that the "Source evaluates to an error". Just click Yes.

Now when you change the selection in B, the drop-down list in C will update.

If that didn't make sense, send me an e-mail/message and I'll send you a file with an example.

 

[jonhall]

That's brilliant - it works perfectly.

Thanks very much,

Jon

 

[David20]

Glad to help. If I remember correctly, it was someone on this board who first taught me this trick.