I am trying to create a dashboard that will show me the totals of multiple pages

Lockie Stewart-Baker

Hi all,

I am trying to create a dashboard that will show me the totals of multiple pages.

I want to be able to add rows to the bottom of the sheets and they get counted in the total.

Paul Newcome

You are going to want to create those totals on each sheet then use cell linking or cross sheet references to pull all of the individual metrics sections into a single sheet.

Lockie Stewart-Baker

So i have created a row that has the formula =sum() and it adds up. The problem i now have is when i want to add a row above this it doesn't auto fill the formulas.

Paul Newcome

Ah. Ok. You actually have a few different options. I personally like to use "helper" columns and then hide those columns to help keep the sheet looking clean.

Another option... You can enter a formula into a cell that is within a range being referenced, but you can only do this once in a column without creating a #CIRCULAR REFERENCE error.

This means that your range can be [Column Name]:[Column Name] and not specify row numbers.

A third option would be to use the top row of the sheet as a sort of summary row and indent all rows beneath. This allows you to leverage the CHILDREN function to include new rows so long as they are indented below the "Parent" row.

=SUM(CHILDREN())

Paul Newcome

You are going to want to create those totals on each sheet then use cell linking or cross sheet references to pull all of the individual metrics sections into a single sheet.

Lockie Stewart-Baker

Ok Thanks, and then add a new row above my total line so it keeps adding up?

Paul Newcome

I'm not sure I understand you last question.

Lockie Stewart-Baker

So i have created a row that has the formula =sum() and it adds up. The problem i now have is when i want to add a row above this it doesn't auto fill the formulas.

Paul Newcome

Ah. Ok. You actually have a few different options. I personally like to use "helper" columns and then hide those columns to help keep the sheet looking clean.

Another option... You can enter a formula into a cell that is within a range being referenced, but you can only do this once in a column without creating a #CIRCULAR REFERENCE error.

This means that your range can be [Column Name]:[Column Name] and not specify row numbers.

A third option would be to use the top row of the sheet as a sort of summary row and indent all rows beneath. This allows you to leverage the CHILDREN function to include new rows so long as they are indented below the "Parent" row.

=SUM(CHILDREN())

Lockie Stewart-Baker

Thanks heaps. I like your helper column idea!!!

Paul Newcome

Happy to help! 👍️

Lockie Stewart-Baker

New question!! I want to set it so the automation moves certain lines to another sheet. Once it is there i want a total of all the costs. I have tried a few different ways of doing it but it always inserts the new line below everything so i not included in the sum formula.

Any tricks for solving that?

Tried using summary and getting it to do a sum of a whole column but it won't let me do that.

Paul Newcome

A sheet summary field SHOULD work. What is the exact formula you are using that isn't working?

Additionally, you could insert a new row at the top and put the formula there referencing the entire column.

Lockie Stewart-Baker

the formula i am trying to use is =sum([total cost]:[total cost]). Total coast been the column i am tying to sum.

Paul Newcome

What is the issue you are having? Is it not giving you the correct amount, or are you getting an error?