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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Do you have a sheet called Firstsheet & another called Lastsheet?
 

PieterLambrechts

New Member
Joined
Feb 22, 2021
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
What is the formula with the actual sheet names?
 

PieterLambrechts

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

ADVERTISEMENT

The formula is
=SUM(Template:Reserve4!H26)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
That works fine for me. Is that the actual formula you are using?
Also are you using an English language version of Excel?
 

PieterLambrechts

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

ADVERTISEMENT

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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
In that case check that H26 does not have the #NAME? error on any of the sheets.
 

PieterLambrechts

New Member
Joined
Feb 22, 2021
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,406
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,261
Messages
5,635,135
Members
416,842
Latest member
Ateen4ever

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
Top