Dependent Data Validation List from Different worksheets

Hi!

I am building a document which has several dependent variables. The document is so structured that values are pulled in different cells based on other cell values. While I have been successful in implementing most of the requirements, there is one place I am stuck at.

I need to form dependent data validation lists which pulls data from a different worksheet for each value in the preceding list. I have enclosed a simplified form of the sheet. Here I would like to mention that I cannot change the structure of the workbook / worksheet and it has to stay as is. Also, I am not averse to using VBA but would prefer if the result can be obtained using a combination of formulas.

The enclosed workbook is divided into the following parts:

1. A main DATA sheet which provides input on 2 lists on other sheets - Continents and Currencies - as well as the exchange information
2, A different sheet for each continent which lists information on the countries in each.
3. A Calculation sheet which has 3 data validation lists - for continents, countries and currencies.

Now, if all the data would have been in one sheet, it was ok. But since there are 5 different sheets for the same data, the data validation in the 'Calculation' sheet has to be such that:

a. On selecting the continent, the data list under 'Country' should pull data from relevant sheet.
b. The formula used under 'Population' should get updated to get the data from the relevant sheet
c. The formula under 'Per Capita GDP' should be updated to get data from relevant sheet depending on the currency selected.

Note: The data in all the sheets will be similarly structured.

Hope I have been able to explain the problem and the solution required.

Any help will be appreciated!!

In Data sheet put Continents F2:J2 and change sheet name Europé to Europe

Then

F3
Formula: [Select Code] copy to clipboard

=IF(ROWS(F$3:F3)-1 < COUNTA(INDIRECT("'"&F$2&"'!A:A"))-1,INDEX(INDIRECT("'"&F$2&"'!A:A"),ROWS(F$3:F3)+2),"")



drag towards the cell and down!!

Now select F2:J13 and press Control+Shift+F3 uncheck left column and then ok.

In calculation sheet

B2 create drop down press Alt->V->V and put at source =INDIRECT($A2) and ok drag down.

Check the attached file hope this will help.

To get price:

in D2


=IFERROR(INDEX(INDIRECT("'" &$B2 &"'!$C$2:$I$33"),MATCH($A2,INDIRECT("'" &$B2 &"'!$B$2:B$33"),0),MATCH("Equivalent Price in " & $C2,INDIRECT("'" &$B2 &"'!$C$1:I$1"),0)),"")

Copy down

Create named ranges

e.g. Named Range: China ==> Refers to: =China!$B$1:$I$51

in D2


=IFERROR(INDEX(INDIRECT(B2),MATCH($A2,INDEX(INDIRECT(B2),,1),0),MATCH("Equivalent Price in " & $C2,INDEX(INDIRECT(B2),1,0),0)),"")