Referencing Sheets Which Do Not Exist

thesilkworm

New Member
Joined
Jan 5, 2014
Messages
23
Hi,

I have a workbook to which I am adding a single sheet every week. These weekly sheets will just be named "1", "2", "3", etc.

I also have a summary sheet which I want to display year-to-date totals. The trouble is, the formula which will work at the end of the year (referencing all 53 weekly sheets), is referencing sheets which do not yet exist.

Is there any way to make, for example, the following simplified formula work properly if sheets "1" and "2" do exist, but "3" does not exist:

='1'!A1+'2'!A1+'3'!A1

I want it to ignore the reference to sheet '3' and just give me the results from the first 2 sheets. I have tried IFERROR('3'!A1,0) instead of '3'!A1, but even this just asks me which sheet I am referencing.

Does anyone have any ideas? Thanks for any help.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Welcome to the board.

Here's what I'd do.

Create 2 dummy sheets.
One named Start (or whatever you want)
One named End (or whatever you want)
Leave these 2 sheets blank

Put the Start Sheet to the LEFT of sheet 1
Put the End sheet to the RIGHT of sheet 2

Then on you summary sheet, use
=SUM('Start:End'!A1)

Then as you add sheets, make sure you add them 'between' the Start and End sheets.
 
Upvote 0
Thanks for the quick answer. That does indeed work perfectly for the example formula I posted, and it's something that is very useful for me to know. However, I actually think it won't work for my 'real' formula, which I probably should have just included to begin with - sorry about that. Here is a real formula from my workbook:

=SUM('1'!F11:'1'!J13)+SUM('2'!D11:'2'!J13)+SUM('3'!D11:'3'!J13)+SUM('4'!D11:'4'!J13)+SUM('5'!D11:'5'!H13)

As you can see, it's not always the same range which I'm referencing. It's D11:J13 when the full week falls in the same month, but you can see that I have an F11:J13 at the start and a D11:H13 at the end, so I do actually have to reference specific sheets rather than just a range of sheets.

Is there any way around this?
 
Upvote 0
In that case, I'd put the sum on each individual sheet, in say K13 (or wherever you want)
Then do
=SUM('Start:End'!K13)


I would imagine you would want to know the individual sums for each sheet as well anyway...
Usually when you have a need for a 'Year to date' total, you also have a need for a total of each month/week/day
 
Upvote 0

Forum statistics

Threads
1,196,047
Messages
6,013,084
Members
441,747
Latest member
darkman77

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