Excel: Dependent drop-down calculations through multiple sheets (with different columns position)

Question

What I am trying to do is doing some calculations (on main sheet as down in SS) through different sheets with similar structure in the same Workbook, so once when I pick something from drop-down it should calculate for instance Total Tonnage through all the sheets and it will give me the results on main page (J4). There are more fields to calculate, but I just need another idea or opinion how to proceed..The sheets are similar but not consistent, combined numbers/text, just some random data actually.

Here are the formulas what I have been using on Column J4 - J7

J4 =IF($A$4=Calculation!$A$2&" ";SUM(tbl_CS1B[Menge '[t']]);IF($A$4=Calculation!$A$4&" ";SUM(tbl_CS2A[Menge '[t']]);IF($A$4=Calculation!$A$7&" ";SUM(tbl_CS3E[Menge '[t']]);IF($A$4=Calculation!$A$8&" ";SUM(tbl_CS3F[Menge '[t']]);IF($A$4=Calculation!$A$9&" ";SUM(tbl_CS3G[Menge '[t']]);IF($A$4=Calculation!$A$10&" ";SUM(tbl_CS3H[Menge '[t']]);IF($A$4=Calculation!$A$11&" ";SUM(tbl_CS3K[Menge '[t']]);IF($A$4=Calculation!$A$12&" ";SUM(tbl_CS3P[Menge '[t']]);IF($A$4=Calculation!$A$15&" ";SUM(tbl_CS7A[Menge '[t']]);IF($A$4=Calculation!$A$16&" ";SUM(tbl_CS7B[Menge '[t']]);IF($A$4=Calculation!$A$17&" ";SUM(tbl_CS7D[Menge '[t']]);IF($A$4=Calculation!$A$18&" ";SUM(tbl_CS7E[Menge '[t']]);""))))))))))))

J5 =IF($A$4=Calculation!$A$2&" ";SUMPRODUCT(1/COUNTIF(tbl_CS1B[Charge];tbl_CS1B[Charge]));IF($A$4=Calculation!$A$4&" ";SUMPRODUCT(1/COUNTIF(tbl_CS2A[Charge];tbl_CS2A[Charge]));IF($A$4=Calculation!$A$7&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3E[Charge];tbl_CS3E[Charge]));IF($A$4=Calculation!$A$8&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3F[Charge];tbl_CS3F[Charge]));IF($A$4=Calculation!$A$9&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3G[CS3G Charge];tbl_CS3G[CS3G Charge]));IF($A$4=Calculation!$A$10&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3H[Charge];tbl_CS3H[Charge]));IF($A$4=Calculation!$A$11&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3K[Charge];tbl_CS3K[Charge]));IF($A$4=Calculation!$A$12&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3P[Charge];tbl_CS3P[Charge]));IF($A$4=Calculation!$A$15&" ";SUMPRODUCT(1/COUNTIF(tbl_CS7A[Charge];tbl_CS7A[Charge]));IF($A$4=Calculation!$A$16&" ";SUMPRODUCT(1/COUNTIF(tbl_CS7B[Charge];tbl_CS7B[Charge]));IF($A$4=Calculation!$A$17&" ";SUMPRODUCT(1/COUNTIF(tbl_CS7D[Charge];tbl_CS7D[Charge]));IF($A$4=Calculation!$A$18&" ";SUMPRODUCT(1/COUNTIF(tbl_CS7E[Charge];tbl_CS7E[Charge]));""))))))))))))

J6 =IF($A$4=Calculation!$A$2&" ";SUMPRODUCT((tbl_CS1B[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS1B[Rohstoff-Charge];tbl_CS1B[Rohstoff-Charge]&""));IF($A$4=Calculation!$A$4&" ";SUMPRODUCT((tbl_CS2A[Rohware Charge]<>"")/COUNTIF(tbl_CS2A[Rohware Charge];tbl_CS2A[Rohware Charge]&""));IF($A$4=Calculation!$A$7&" ";SUMPRODUCT((tbl_CS3E[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS3E[Rohstoff-Charge];tbl_CS3E[Rohstoff-Charge]&""));IF($A$4=Calculation!$A$8&" ";SUMPRODUCT((tbl_CS3F[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS3F[Rohstoff-Charge];tbl_CS3F[Rohstoff-Charge]&""));IF($A$4=Calculation!$A$9&" ";SUMPRODUCT((tbl_CS3G[Rohware Charge]<>"")/COUNTIF(tbl_CS3G[Rohware Charge];tbl_CS3G[Rohware Charge]&""));IF($A$4=Calculation!$A$10&" ";SUMPRODUCT((tbl_CS3H[Rohrware Charge]<>"")/COUNTIF(tbl_CS3H[Rohrware Charge];tbl_CS3H[Rohrware Charge]&""));IF($A$4=Calculation!$A$11&" ";SUMPRODUCT((tbl_CS3K[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS3K[Rohstoff-Charge];tbl_CS3K[Rohstoff-Charge]&""));IF($A$4=Calculation!$A$12&" ";SUMPRODUCT((tbl_CS3P[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS3P[Rohstoff-Charge];tbl_CS3P[Rohstoff-Charge]&""));IF($A$4=Calculation!$A$15&" ";SUMPRODUCT((tbl_CS7A[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS7A[Rohstoff-Charge];tbl_CS7A[Rohstoff-Charge]&""));IF($A$4=Calculation!$A$16&" ";SUMPRODUCT((tbl_CS7B[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS7B[Rohstoff-Charge];tbl_CS7B[Rohstoff-Charge]&""));IF($A$4=Calculation!$A$17&" ";SUMPRODUCT((tbl_CS7D[Rohware Charge]<>"")/COUNTIF(tbl_CS7D[Rohware Charge];tbl_CS7D[Rohware Charge]&""));IF($A$4=Calculation!$A$18&" ";SUMPRODUCT((tbl_CS7E[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS7E[Rohstoff-Charge];tbl_CS7E[Rohstoff-Charge]&""));""))))))))))))

J7 =IF($A$4=Calculation!$A$2&" ";COUNTA(tbl_CS1B[Faß]);IF($A$4=Calculation!$A$4&" ";COUNTA(tbl_CS2A[Faß]);IF($A$4=Calculation!$A$7&" ";COUNTA(tbl_CS3E[Faß]);IF($A$4=Calculation!$A$8&" ";COUNTA(tbl_CS3F[Faß]);IF($A$4=Calculation!$A$9&" ";COUNTA(tbl_CS3G[Faß]);IF($A$4=Calculation!$A$10&" ";COUNTA(tbl_CS3H[Faß]);IF($A$4=Calculation!$A$11&" ";COUNTA(tbl_CS3K[Faß]);IF($A$4=Calculation!$A$12&" ";COUNTA(tbl_CS3P[Faß]);IF($A$4=Calculation!$A$15&" ";COUNTA(tbl_CS7A[Faß]);IF($A$4=Calculation!$A$16&" ";COUNTA(tbl_CS7B[Faß]);IF($A$4=Calculation!$A$17&" ";COUNTA(tbl_CS7D[Faß]);IF($A$4=Calculation!$A$18&" ";COUNTA(tbl_CS7E[Faß]);""))))))))))))

So, as you can see it works but the formulas are kilometers long. Does anyone can help with any other solution(s) or approaches to solve similar things through whole workbook? My WB is formatted as a table from Power Query which gives me more possibilties, like Pivot tables and so on. But I would like to have those infos spilled on one page not on XY pages, like one pivot sheet for one material and so on..

Can that be achieved using INDIRECT function or some other combination?

I am using Office 2016.

At a glance, it seems like you can shave some space by replacing the nested IFs with CHOOSE(MATCH)). If your table names follow a logical naming convention then you may be able to build a reference with INDIRECT and OFFSET. But this all seems like unnecessary complexity compared to using simple lookups on a master table built with PowerQuery. YMMV. — ProfoundlyOblivious
@ProfoundlyOblivious Hmmm, maybe I should give a more try to PQ itself..you might be right — Mirza

Alan Alan · Accepted Answer · 2020-09-16T11:58:14

Power Query and Power Pivot are likely your best options.

You can use INDIRECT, it's a good solution but adds overhead to the calculations.

You could also put the repetitive parts of the calculation into a VBA function and pass it the sheet name but that adds VBA to the file.

Power Query can read data from tables, including multiple tables in one workbook, and then you can append the data together in the data model. You can add DAX measures for your calculations. This also has the side-effect of making it easier to port to PowerBI because the data model exists (if you choose to go down that route).

It's hard to go into detail because I can't see the whole workbook. I'm also not sure whether the screenshot is of the Calculations worksheet or one of the Materials worksheet. And I don't know what is meant by "just for one instance nearby".

If you have an anonymised copy of the full workbook I can take a more detail look at it.

Excel: Dependent drop-down calculations through multiple sheets (with different columns position)

1 Answers