Lo que estoy tratando de hacer es realizar algunos cálculos (hoja principal) a través de diferentes hojas con estructuras similares en el mismo libro de trabajo, así que una vez que elija algo de la lista desplegable debería calcular, por ejemplo, Total Tonelaje a través de todas las hojas y me dará los resultados en la página principal (J4).
Hay más campos para calcular, pero solo necesito otra idea u opinión sobre cómo proceder.. Las hojas son similares pero no consistentes, números/texto combinados, en realidad solo algunos datos aleatorios.
Aquí están las fórmulas que he estado utilizando en las columnas J4 - J7
J4 =IF($A$4=Calculo!$A$2&" ";SUM(tbl_CS1B[Menge '[t']]);IF($A$4=Calculo!$A$4&" ";SUM(tbl_CS2A[Menge '[t']]);IF($A$4=Calculo!$A$7&" ";SUM(tbl_CS3E[Menge '[t']]);IF($A$4=Calculo!$A$8&" ";SUM(tbl_CS3F[Menge '[t']]);IF($A$4=Calculo!$A$9&" ";SUM(tbl_CS3G[Menge '[t']]);IF($A$4=Calculo!$A$10&" ";SUM(tbl_CS3H[Menge '[t']]);IF($A$4=Calculo!$A$11&" ";SUM(tbl_CS3K[Menge '[t']]);IF($A$4=Calculo!$A$12&" ";SUM(tbl_CS3P[Menge '[t']]);IF($A$4=Calculo!$A$15&" ";SUM(tbl_CS7A[Menge '[t']]);IF($A$4=Calculo!$A$16&" ";SUM(tbl_CS7B[Menge '[t']]);IF($A$4=Calculo!$A$17&" ";SUM(tbl_CS7D[Menge '[t']]);IF($A$4=Calculo!$A$18&" ";SUM(tbl_CS7E[Menge '[t']]);""))))))))))))
J5 =IF($A$4=Calculo!$A$2&" ";SUMPRODUCT(1/COUNTIF(tbl_CS1B[Charge];tbl_CS1B[Charge]));IF($A$4=Calculo!$A$4&" ";SUMPRODUCT(1/COUNTIF(tbl_CS2A[Charge];tbl_CS2A[Charge]));IF($A$4=Calculo!$A$7&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3E[Charge];tbl_CS3E[Charge]));IF($A$4=Calculo!$A$8&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3F[Charge];tbl_CS3F[Charge]));IF($A$4=Calculo!$A$9&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3G[CS3G Charge];tbl_CS3G[CS3G Charge]));IF($A$4=Calculo!$A$10&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3H[Charge];tbl_CS3H[Charge]));IF($A$4=Calculo!$A$11&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3K[Charge];tbl_CS3K[Charge]));IF($A$4=Calculo!$A$12&" ";SUMPRODUCT(1/COUNTIF(tbl_CS3P[Charge];tbl_CS3P[Charge]));IF($A$4=Calculo!$A$15&" ";SUMPRODUCT(1/COUNTIF(tbl_CS7A[Charge];tbl_CS7A[Charge]));IF($A$4=Calculo!$A$16&" ";SUMPRODUCT(1/COUNTIF(tbl_CS7B[Charge];tbl_CS7B[Charge]));IF($A$4=Calculo!$A$17&" ";SUMPRODUCT(1/COUNTIF(tbl_CS7D[Charge];tbl_CS7D[Charge]));IF($A$4=Calculo!$A$18&" ";SUMPRODUCT(1/COUNTIF(tbl_CS7E[Charge];tbl_CS7E[Charge]));""))))))))))))
J6 =IF($A$4=Calculo!$A$2&" ";SUMPRODUCT((tbl_CS1B[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS1B[Rohstoff-Charge];tbl_CS1B[Rohstoff-Charge]&""));IF($A$4=Calculo!$A$4&" ";SUMPRODUCT((tbl_CS2A[Rohware Charge]<>"")/COUNTIF(tbl_CS2A[Rohware Charge];tbl_CS2A[Rohware Charge]&""));IF($A$4=Calculo!$A$7&" ";SUMPRODUCT((tbl_CS3E[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS3E[Rohstoff-Charge];tbl_CS3E[Rohstoff-Charge]&""));IF($A$4=Calculo!$A$8&" ";SUMPRODUCT((tbl_CS3F[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS3F[Rohstoff-Charge];tbl_CS3F[Rohstoff-Charge]&""));IF($A$4=Calculo!$A$9&" ";SUMPRODUCT((tbl_CS3G[Rohware Charge]<>"")/COUNTIF(tbl_CS3G[Rohware Charge];tbl_CS3G[Rohware Charge]&""));IF($A$4=Calculo!$A$10&" ";SUMPRODUCT((tbl_CS3H[Rohrware Charge]<>"")/COUNTIF(tbl_CS3H[Rohrware Charge];tbl_CS3H[Rohrware Charge]&""));IF($A$4=Calculo!$A$11&" ";SUMPRODUCT((tbl_CS3K[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS3K[Rohstoff-Charge];tbl_CS3K[Rohstoff-Charge]&""));IF($A$4=Calculo!$A$12&" ";SUMPRODUCT((tbl_CS3P[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS3P[Rohstoff-Charge];tbl_CS3P[Rohstoff-Charge]&""));IF($A$4=Calculo!$A$15&" ";SUMPRODUCT((tbl_CS7A[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS7A[Rohstoff-Charge];tbl_CS7A[Rohstoff-Charge]&""));IF($A$4=Calculo!$A$16&" ";SUMPRODUCT((tbl_CS7B[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS7B[Rohstoff-Charge];tbl_CS7B[Rohstoff-Charge]&""));IF($A$4=Calculo!$A$17&" ";SUMPRODUCT((tbl_CS7D[Rohware Charge]<>"")/COUNTIF(tbl_CS7D[Rohware Charge];tbl_CS7D[Rohware Charge]&""));IF($A$4=Calculo!$A$18&" ";SUMPRODUCT((tbl_CS7E[Rohstoff-Charge]<>"")/COUNTIF(tbl_CS7E[Rohstoff-Charge];tbl_CS7E[Rohstoff-Charge]&""));""))))))))))))
J7 =IF($A$4=Calculo!$A$2&" ";COUNTA(tbl_CS1B[Faß]);IF($A$4=Calculo!$A$4&" ";COUNTA(tbl_CS2A[Faß]);IF($A$4=Calculo!$A$7&" ";COUNTA(tbl_CS3E[Faß]);IF($A$4=Calculo!$A$8&" ";COUNTA(tbl_CS3F[Faß]);IF($A$4=Calculo!$A$9&" ";COUNTA(tbl_CS3G[Faß]);IF($A$4=Calculo!$A$10&" ";COUNTA(tbl_CS3H[Faß]);IF($A$4=Calculo!$A$11&" ";COUNTA(tbl_CS3K[Faß]);IF($A$4=Calculo!$A$12&" ";COUNTA(tbl_CS3P[Faß]);IF($A$4=Calculo!$A$15&" ";COUNTA(tbl_CS7A[Faß]);IF($A$4=Calculo!$A$16&" ";COUNTA(tbl_CS7B[Faß]);IF($A$4=Calculo!$A$17&" ";COUNTA(tbl_CS7D[Faß]);IF($A$4=Calculo!$A$18&" ";COUNTA(tbl_CS7E[Faß]);""))))))))))))
Entonces, como puedes ver, funciona pero las fórmulas son kilómetros de largo. ¿Alguien puede ayudar con alguna otra solución o enfoques para resolver cosas similares en todo el libro de trabajo?
Mi libro de trabajo está formateado como una tabla desde Power Query que me da más posibilidades, como tablas dinámicas, etc. Pero me gustaría tener esa información desplegada en una página no en XY páginas, como una hoja dinámica para un material y así sucesivamente..
¿Es posible lograr esto usando la función INDIRECT o alguna otra combinación?
Estoy utilizando Office 2016.
