CODEWITHSUNDEEP
excel
K20GH
K20GH

Reputation: 6281

Sum of duplicate named range across multiple sheets?

Ive got X amount of sheets in my excel document. On all sheets, there is a named range of:

TotalSupportCost

On a master sheet, how can I do something like:

Total value of all TotalSupportCost across every worksheet

This way, whenever a sheet is duplicated the total will dynamically increase

Upvotes: 1

Views: 734

Answers (1)

K_B
K_B

Reputation: 3678

unfortunately you cannot use something like '=SUM('Sheet1:Sheet3'!testname)', although Excel itself will give that when you hold the shift key and select all sheets...

A worksheet function what you can use to a limited extend is INDIRECT() however you still wont be able to learn all the worksheet names of the worksheets in your workbook.

This leaves you to the resort of using VBA. In VBA you can actually loop through all worksheets and retrieve (and sum up) all the values of a certain cell or of a named range. When you make this function Public you will be able to call it as if it were a worksheet function itself!

Upvotes: 1

Related Questions