# Referencing Sheets Which Do Not Exist

#### thesilkworm

##### New Member
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.

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?

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

That method makes more sense than what I originally planned to do. Thanks for your help.

You're welcome, glad to help out.

Replies
5
Views
169
Replies
9
Views
233
Replies
9
Views
301
Replies
28
Views
504
Replies
5
Views
110

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.

### Which adblocker are you using?

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

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