Excel Indirect function under data validation

Question

Hi Execel expert

I am creating a very simple table to pull two lists from seperate excel sheet using data validation function.

I would like to pull the second list result based on the first list in the table using Indiect. But I am not able to do it. Excel keeps showing me  

"The source currently evaluates to an error". I recreate the lists, but still it is showing me the same error message. I dont know how I can fix it.

Ex: the first list is area

 APAC,GCR,WE,MEA

The second list is the contry under that area

China,Hongkong, Aurealia, Korea, etc.

Can anyone help?

Thanks

Answer 1

Hi,

For the INDIRECT() function to work, you have to first define Named Ranges.  You may also refer to my solution at this link - http://www.ashishmathur.com/secondary-validation-cell-entry-to-update-when-primary-validation-cell-changes/

Hope this helps.

Answer 2

Hi Yushinkuo.

First, to use indirect in the way you've specified above. You'll need the cell D5 to contain the cell range you want to pick.

Notice that cell D5 has the E2:E6 in it, which represents the cell range for the countries under CEE.

Answer 3

I believe you want to create data validation with dependent list.

You may like to explore this thread where a same question has been answered by me.

http://answers.microsoft.com/en-us/office/forum/office\_2010-excel/insert-condition-in-data-validation-list/d343922f-10a7-47ac-9df5-48734d8323a2

Assume your first list range is B2:B6, Give it a name "APAC". Like that give Name for each of your list in column C to F.  Like you have to give range E2:E6 as "CEE"

You have alread given name Area to your range B1:F1

Now suppose your first data validation cell is A1 then in List/Allow/give formula as =Area

and then go to your 2nd validation say its B1, and here in data validation list/Allow give formula as =INDIRECT(A1)     means reference of your first data validation cell.

Answer 4

Not exactly sure of what you want to do but D5 is currently blank so indirect(D5) definitely gives am error or were you intending to use another cell and not D5?