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!!
https://1drv.ms/x/s!AtcW5qbfR3ClgrkXCUGsU45Uj5Qxyg
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!!
https://1drv.ms/x/s!AtcW5qbfR3ClgrkXCUGsU45Uj5Qxyg
