Referencing Sheets Which Do Not Exist

[thesilkworm]

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.

 

[Jonmo1]

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.

 

[thesilkworm]

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?

 

[Jonmo1]

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

 

[thesilkworm]

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

 

[Jonmo1]

You're welcome, glad to help out.