Hi there guys.
I have a table on a page, called the "Final Table", and right below it there's a "total" row. Now, my plan is to have this table dynamically resize by taking its data from different other data tables in another sheets. I'm going to have this sheet write protected, and whenever I feed more data into the data tables this "Final Table" might increase in rows.
What I want to do is, if possible without VBA due to security workarounds being necessary, make the "Total" row automatically move downwards or upwards as the "Final Table" increases or decreases in row count. Is this at all possible or am I just dreaming?
Example of how it looks (although the data tables might be on completely different sheets, even workbooks depending on the situation):
1) "Tiny Table" reads from "Data table 1" and populates its rows. "Total" adjusts automatically:
2) Only adding one row to "Data table 1" is causing "Tiny Table" to become one row longer and almost overwriting the "Total" row:
So, what I want is the "Total" row to always stay with one row between itself and "Tiny Table", but avoid VBA by any means necessary (if possible). Any tricks are welcome. Haven't got it working by grouping or anything.
Best regards,
P
I have a table on a page, called the "Final Table", and right below it there's a "total" row. Now, my plan is to have this table dynamically resize by taking its data from different other data tables in another sheets. I'm going to have this sheet write protected, and whenever I feed more data into the data tables this "Final Table" might increase in rows.
What I want to do is, if possible without VBA due to security workarounds being necessary, make the "Total" row automatically move downwards or upwards as the "Final Table" increases or decreases in row count. Is this at all possible or am I just dreaming?
Example of how it looks (although the data tables might be on completely different sheets, even workbooks depending on the situation):
1) "Tiny Table" reads from "Data table 1" and populates its rows. "Total" adjusts automatically:
| Bok1 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Tiny Table | Amount | Cost | Data table 1 (in another sheet) | Amount | Cost | |||
| 2 | Product A | 1 | 50 | Product A | 1 | 50 | |||
| 3 | Product F | 2 | 100 | Product F | 2 | 100 | |||
| 4 | |||||||||
| 5 | Total | 3 | 250 | ||||||
Blad1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B5 | B5 | =SUM(Tabell1[Amount]) |
| C5 | C5 | =SUM((B2*C2)+(B3*C3)) |
2) Only adding one row to "Data table 1" is causing "Tiny Table" to become one row longer and almost overwriting the "Total" row:
| Bok1 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Tiny Table | Amount | Cost | Data table 1 (in another sheet) | Amount | Cost | |||
| 2 | Product A | 1 | 50 | Product A | 1 | 50 | |||
| 3 | Product F | 2 | 100 | Product F | 2 | 100 | |||
| 4 | Product Q | 1 | 200 | Product Q | 1 | 200 | |||
| 5 | Total | 4 | 450 | ||||||
Blad1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B5 | B5 | =SUM(Tabell1[Amount]) |
| C5 | C5 | =SUM((B2*C2)+(B3*C3)+(B4*C4)) |
So, what I want is the "Total" row to always stay with one row between itself and "Tiny Table", but avoid VBA by any means necessary (if possible). Any tricks are welcome. Haven't got it working by grouping or anything.
Best regards,
P
