Data Query based on the drop down selection based on the dependent/independent drop down validation list

Hi Guys,

Need help with the Data Query based on the drop down selection based on the dependent/independent drop down validation list

Sample File attached

I tried various formulas but was not able to get it working.

I cannot use INDIRECT because these drop down list are independent (or may be dependent)

I have a Metrics Table in my Consolidated Sheet and i have 10 teams (Team 1, Team 2...Team 9), 3 Environments (Production, Non Prod, No Field, All).

I have 10 other Sheets (Team 1, Team 2... Team 9). Each Sheet has same Metrics Table for All, Production, Non Prod, No Field. From Team 1 Sheet to Team 9 Sheet same format.

In the Consolidated, it is the same Metrics Table and format, however i have put 2 drop down Validation Lists. Independent Drop Downs (INDIRECT is not required) or what if that is made as related field and display only when the other Drop down is selected. This looks like a good one. So i will go with the dependent dropdowns

Requirement is, when i select the Team from the Team Drop Down List, and based on the selection from the dependent drop down list that is (Environment) it should pull the data from the respective teams sheet.

Not sure what is the best approach to get this working. Tried vlooks but was not able to achieve.

Hope there is a alternative to get this working.

Regards Ajay

Team, do we have any solution for this please?

guys any solution for this

Sorry for the delay in responding. Many Thanks Glenn it worked like a charm.

However the number of columns has increased that has to be queried. Earlier in the sample it was 10 columns. I have seen you have used a =check (indirect) function to check the number of columns that have to queried.

Current formula is returning only 10 column values. Please help in modifying the formula to query 24 columns.

Thanks Glenn,

Here is the new sample attached.

Dashboard Sheet:

Columns B to F --> These are the Quarterly numbers that are summed up based on the data query numbers in the range (G to AD)

Columns G to AD --> Month on Month numbers to be queried based on the drop down selection

Columns AE to AM --> These are the Quarterly numbers that are summed up based on the data query number in the range (G to AD)

Team 1 Sheet:

Exact same data range replicated in the Team1 to Team 9 (for sample I have put only 1).

However I need to display or query only these number rest I will calculate using sum or other formulas. Also there will be some calculation in the rows as well. I mean to say Row 1 should be queried by formula but Row 2 has a % calculation and based on the values from Row 1 and Row 2 I will calculate the value in the Row 3,

Row 4, 5,6 to be queried by formula.

Hope you can help on this.

Thanks in advance.

Regards

Ajay