Excel - COLUMN Function Problem - Expert Solution

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
User 09/03/2018 - 09:03
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc