Troubleshooting: sum one cell across multiple worksheets

PieterLambrechts

New Member
Joined
Feb 22, 2021
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
Dear fellow Excel enthusiasts

I am trying to sum one cell across multiple worksheets. The worksheets that I want to include in the sum are all, except the first worksheet (Dashboard is used to display information).

I use the following formula: =SUM(Firstsheet:Lastsheet! H26). I always get the error: #name? I googled for about an hour, tried a bunch of different things (adding ' or " before and after the sheet names for example) but this doesn't work. Does anybody know what I might be doing wrong?

Many thanks in advance!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi & welcome to MrExcel.
Do you have a sheet called Firstsheet & another called Lastsheet?
 
Upvote 0
Hi & welcome to MrExcel.
Do you have a sheet called Firstsheet & another called Lastsheet?
No, but I changed the names of the sheets. Sheet names are in Dutch, and I work in healthcare. I am not allowed to post the workbook online as it contains a lot of confidential (patient) information.
"Firstsheet" is my second sheet in the list of worksheets (dashboard is the first sheet), "lastsheet" is really the last worksheet in the workbook.
 
Upvote 0
What is the formula with the actual sheet names?
 
Upvote 0
That works fine for me. Is that the actual formula you are using?
Also are you using an English language version of Excel?
 
Upvote 0
That works fine for me. Is that the actual formula you are using?
Also are you using an English language version of Excel?
Yeah, I can't find any problems with it either. But still returns an error. This is the exact formula. I use an English language version.
 
Upvote 0
In that case check that H26 does not have the #NAME? error on any of the sheets.
 
Upvote 0
Thanks Fluff, that did it! There were 2 worksheets with the #NAME? error in H26. Fixed the error, now it works like a charm! Many thanks!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top