Index with queries

[ganeshm]

Hi,

I require guidance for the attached excel file.

I have details in sheet1 which has to be published in sheet 2 with index queries.

say..
Note 1 - Select dept.
Note 2 - Select corresponding nationality to the dept.
Note 3 - Select corresponding position to the dept and nationality (a combined query)

How do i proceed with this?

regards,
safiu

 

[NARAYANK991]

Hi ,

See this file.

I have added 3 pivot tables to get the distinct entries in the DEPT , NATIONALITY and Position fields of your table. When ever you make new entries in the table , you should Refresh these pivot tables to keep them up to date.

Narayan

 

[ganeshm]

Thank you mr. Narayan.

It was helpful.

 

[ganeshm]

further to the above excel file.

If i select "FIN" from data validation, i would want only nationality Jordon or British to appear.
Similarly, corresponding position need to appear say accountant and finance manager.

The following formula throws "#ref" error, when i place it in name range.
=INDEX(INDIRECT(M3,TRUE),Table1[#Data],Table1[NATIONALITY])

 

[ganeshm]

hi...
can any experts sort out the above excel book "#ref" error.

 

[SirJB7]

Hi, ganeshm!
Regarding the nationalities issue, you'd have to create a separate table. You may want to give a look at this link to check linked selections with drop down validation lists:
http://chandoo.org/forum/threads/da...tically-shows-the-first-item-in-the-list.4201
Regards

 

[ganeshm]

thank you SirJB7 for your quick response.
This helps a lot.

 

[SirJB7]

Hi, ganeshm!
Glad if it helped you to solve it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!

 

[ganeshm]

Dear SirJB7,

Without using vba code is it possible to use formula in name range?

For instance,
1st drop down - i select Finance -
2nd drop down - since finance dept has only two nationalities (reference sheet 1) say "British" and "Jordanian", will the 2nd drop down show only these nationalities.