Question description:
This user has given permission to use the problem statement for this
blog.
I have a multiple sheet workbook where sheet one is for data entry with conditional drop down menus based on a table. Dropdown Menu in column B is conditional on the choice in dropdown menu in Column A. This works fine. My problem is, I now need a dropdown menu in Column C that is also conditional on the choice in column A, BUT, the lists are on different sheets. I have named ranges for the initial table for Columns A and B, but I don't know how to have a conditional dropdown in column C that looks at different sheets and returns a list based on the choice in column A. In other words, both columns B and C are conditional on the same value in A, but one is looking at a table in the same sheet, whereas the second is looking across multiple sheets for the value in A and then returning the correct list.
Solved by E. E. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
09/03/2018 - 09:03
Hi
User
09/03/2018 - 09:03
hi
Excelchat Expert
09/03/2018 - 09:03
How are you?
User
09/03/2018 - 09:04
good thanks
Excelchat Expert
09/03/2018 - 09:04
I'm reading your problem
Excelchat Expert
09/03/2018 - 09:04
Let's turn out for solution
User
09/03/2018 - 09:09
Problem with a little detail: I need to choose a GENRE in column G, which references the table and looks at column A as a list. Column H used data validation to be conditional; it returns only the choices in the table related to the genre. my problem is, I also have a separate table of featured instruments that is dependent on genre. those lists are all on diff sheets. (sheet 2 here). how can I make the dropdown in column J return the list from sheet 2 labeled featured instruments
Excelchat Expert
09/03/2018 - 09:11
What is the column of sheet 2 that you wish to be in drop down?
Excelchat Expert
09/03/2018 - 09:11
Is it column A?
User
09/03/2018 - 09:11
column D.
User
09/03/2018 - 09:11
but there are 20+ sheets. each has a variable list size in column D
User
09/03/2018 - 09:12
the returned list has to come from column D from the correct sheet, based on the choice in sheet 1, column G
Excelchat Expert
09/03/2018 - 09:13
Please check now Column J in Sheet 1, data validation is applied
User
09/03/2018 - 09:13
great, I see that. What if I change the choice in column G?
Excelchat Expert
09/03/2018 - 09:14
If you will change, there will be no harm
User
09/03/2018 - 09:14
but I want the list to then become the list in column D on sheet BLUES
Excelchat Expert
09/03/2018 - 09:15
Ok i can change that too
User
09/03/2018 - 09:15
how are you changing it? what is the formula
Excelchat Expert
09/03/2018 - 09:15
Let me include both
Excelchat Expert
09/03/2018 - 09:15
No formula is required, you need to do through functions
User
09/03/2018 - 09:15
can you show me the function?
Excelchat Expert
09/03/2018 - 09:17
Please check now
User
09/03/2018 - 09:17
I just changed it back and the list doesn't change
User
09/03/2018 - 09:18
If I choose AMBIENT, list should come from sheet AMBIENT, column D
User
09/03/2018 - 09:18
if I choose BLUES, list should come from sheet BLUES, column D
Excelchat Expert
09/03/2018 - 09:22
Well do you want this validation in same column on the basis of different conditions?
User
09/03/2018 - 09:22
yes. If I choose AMBIENT in G3, go to sheet AMBIENT, return list in column D.
Excelchat Expert
09/03/2018 - 09:22
If you need this condition, I have to perform this function on two different columns then only it will work
User
09/03/2018 - 09:23
If I choose BLUES in G3, go to sheet BLUES, return list in column D
Excelchat Expert
09/03/2018 - 09:23
Let me see my friend
User
09/03/2018 - 09:23
it has to be in one column
Excelchat Expert
09/03/2018 - 09:31
I've tried but there is only single way i.e. input can have either from blues or ambient
User
09/03/2018 - 09:32
ok thanks
Excelchat Expert
09/03/2018 - 09:33
Please tell me what should i do for you?
User
09/03/2018 - 09:33
nothing. need to go somewhere to find the answer
User
09/03/2018 - 09:33
thanks
Excelchat Expert
09/03/2018 - 09:33
Please allow me some more time to work
Excelchat Expert
09/03/2018 - 09:33
Let me try again
Excelchat Expert
09/03/2018 - 09:34
Your non satisfaction is not good for us too, we want you to be happy an satisfied
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
