CODEWITHSUNDEEP
excelexcel-formula
JoB
JoB

Reputation: 1

Single formula in master sheet to add data from the same cell in newly added sheets - Excel

I am looking for an excel formula for a "total" cell in a single master sheet that adds up values across multiple other sheets. I would like this formula to automatically search all sheets in the file for data within this same cell and provide a total so that new sheets can be added and old sheets deleted without having to manually reference each cell across multiple sheets. I am hoping there is an automated way of doing this?

Thank you!

I have tried to manually create a formula but it is very time consuming and inflexible as sheets change.

Upvotes: 0

Views: 89

Answers (1)

Raghav Khemka
Raghav Khemka

Reputation: 46

If you are looking for a formula that you can copy and paste in certain cells in the Master sheet, There is a way of doing that.

Create a Named Range. Go to Formulas-> Define Name . Enter name as sheet_list and value as

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Use the below formula in the Master sheet where you want to have the sum of all numbers calculated from the rest of the sheets in the workbook.

=SUM(N(INDIRECT("'"&INDEX(sheet_list,SEQUENCE(SHEETS()-1,,2))&"'!"&ADDRESS(ROW(B2),COLUMN(B2)))))

(Make sure your master sheet is placed in the beginning of the workbook.)

Change 'B2' with the reference of the cell that you are pasting in. (eg. if you are pasting it in H5, replace B2 with H5). Once done, you can copy paste the formula in other cells of master sheet.

Upvotes: 2

Related Questions