Adding Named Range Across Multiple Sheets

[RossyDownUnder]

I have a workbook that contains multiple sheets to record the jobs that each employee works on and the profit we make from each of those jobs. At the bottom of the list of jobs I have totalled the profits made by that employee and named the cell "TotalProfit" (scope is the sheet name). I want to put the total of these on a summary page so that I can compare it to other data. Each employee will have a different number of jobs at any given time and additional employees will be added from time to time.

Is there a way that I can sum the "TotalProfit" from each sheet.

Thanks in Advance

 

[RoryA]

Is there any pattern to the sheet names?

 

[RossyDownUnder]

Is there any pattern to the sheet names?

No they are the employee names

 

[RoryA]

If you can put a list of them somewhere, you can use that with INDIRECT - e.g.

=SUM(INDIRECT("'"&A2:A20&"'!TotalProfit"))

where A2:A20 contains the sheet names

 

[RossyDownUnder]

If you can put a list of them somewhere, you can use that with INDIRECT - e.g.

=SUM(INDIRECT("'"&A2:A20&"'!TotalProfit"))

where A2:A20 contains the sheet names

Hi RoryA, Thanks for the help but I enter the above, changing the cell references to A4:A6 as my test file only has 3 employees, it returns #VALUE!.

 

[RoryA]

In 2016 you'll probably need to enter it using Ctrl+Shift+Enter not just enter.

 

[RossyDownUnder]

Hi RoryA, Thanks for the help but I enter the above, changing the cell references to A4:A6 as my test file only has 3 employees, it returns #VALUE!.

Thanks RoryA, that has fixed the #value issue but the total showing is only showing the total for the first employee

 

[RoryA]

Odd, it works in 365 (I don't have 2016 any more to test). How about:

=SUM(N(INDIRECT("'"&A2:A20&"'!TotalProfit")))

again entered with Ctrl+Shift+Enter?

 

[RossyDownUnder]

Odd, it works in 365 (I don't have 2016 any more to test). How about:

=SUM(N(INDIRECT("'"&A2:A20&"'!TotalProfit")))

again entered with Ctrl+Shift+Enter?

Thanks RoryA that fixed work. Greatly appreciate your assistance.

 

[Dave Patton]

try the formula with SumProduct
I did not test with earlier versions.

T202311a.xlsm
	B	C	D
1
2
3	50	50	50
4
5b

Cell Formulas
Range	Formula
B3	B3	=SUM(INDIRECT("'"&SheetNames&"'!TotalProfit"))
C3	C3	=SUMPRODUCT(INDIRECT("'"&SheetNames&"'!TotalProfit"))
D3	D3	=SUMPRODUCT(--(INDIRECT("'"&SheetNames&"'!TotalProfit")))

Named Ranges
Name	Refers To	Cells
SheetNames	='5b'!$A$3	B3:D3