Complicated In-cell reference question

[Guest]

Im currently working on a spreadsheet that uses several in-cell pulldowns. My
workbook contains 10 different tabs. Each tab has the same fields but the
drop downs are different (tab specific). To do this I have just created
different lists on my data tab and everything works fine. However, what I
would like to do is be able to merge all my tabs into one and make a master
pull down field in my first column. In doing so, I want to be able to have
all my fields that use pulldowns be able to recognize what list to use based
on the entry in the first column. I’ve tried several different ways to do
this I can not figure it out. So, if any of you know how to do this and would
not mind trying to explain I would be very appreciative. Thanks!

 

[Debra Dalgleish]

You can create dependent data validation dropdowns, as described here:

http://www.contextures.com/xlDataVal02.html

 

[Guest]

Thank you. That did help. However, whats still getting me is that he drop
list I will be selecting needs to control 3 different columns. For example,
what i select in column A will be affecting the drop down in columns B,C and
.. I cant seem to find anything that deals with that. Can you even do that?
Thanks again.

 

[Debra Dalgleish]

If you give an example of what should happen in the 3 different columns,
someone may be able to help.

But if you need three different dependent lists, based on the selection
in column A, you could create different named lists to use as the
source. For example, if you select "Pen" in column A, and want a colour
list in column B, create a list of colours named PenColour.
In the data validation dialog box, allow List, and use the formula:
=INDIRECT(A2 & "Colour")

Or if several items have the same list of colours, you could use a
lookup table to return the list name. For example:
=INDIRECT(VLOOKUP(A2,ColoursLU,2,0))
where ColoursLU is a table with item names in column 1 and colour list
names in column 2:

Pen ColoursA
Chair ColoursB
Label ColoursB
Paper ColoursA