Referencing sheets that don't yet exist?

[Kev Hardy]

Hi all,

I have a workbook that we use for our rota. The template contains 5 sheets: 'Staff Stats', 'Week 1 Master', Week 2 Master', 'Start', 'End'
The Workbook is used to record each months schedule.

There are two Week # Master pages as the rota is fortnightly - workers have every other weekend off and different days off in the week.

A new sheet is created for each week of the month by copying the appropriate Master (depending on whether the month starts with a week 1 or week 2 format) and placing between the 'Start' and 'End' sheets. This was done so that monthly data could be collected on the 'Staff Stats' sheet which did not include shift data from the Master sheets (so it only looks at cell references between the 'Start' and 'End' sheets).

My problem is that I have been asked for the Staff Stats sheet to collect the weekly data (as in week 1, week 2, etc) and I am struggling to get my head around this. My problem appears to be that as the sheets don't exist until they are created and named I can't create cells that reference the data (because it isn't there).

Is there a way a referencing sheets in a workbook without using the sheet name? As in "This cell equals the value of B1 of the 5th sheet in the workbook"

Kev

 

[oldbrewer]

if in A1 you have =greensheet!C4 and that sheet does not exist yet you will get an error

so use =if(iserror(greensheet!C4),"sheet not created yet",greensheet!C4)

 

[Kev Hardy]

if in A1 you have =greensheet!C4 and that sheet does not exist yet you will get an error

so use =if(iserror(greensheet!C4),"sheet not created yet",greensheet!C4)

Thanks oldbrewer. I'm not back in the office until Monday now but will give this a go.

I get the feeling that this does mean that any new sheets will have to have fixed names such as 'Week 1', 'Week 2', 'Week 3' etc.
What I have been doing up to now has been naming the sheets with the date range, i.e. 'Feb 7-13' (the week runs from Sunday to Saturday). It would be useful to be able to keep this but means that the sheet name will be an unknown variable. Is there any way to do this or perhaps reference the first part of a sheets name? i.e. call the sheet 'Week 1 Feb 7-13', Week 2 Feb 14-20' etc.

Thanks

Kev

 

[oldbrewer]

I do not see a problem naming a sheet Feb 7-13 and the next one Feb 14-20

you know that eg Feb 7-13 is week 6 (or whatever)

not now sure what the issue is...

 

[Jonmo1]

This method doesn't quite work for me.

=if(iserror(greensheet!C4),"sheet not created yet",greensheet!C4)

If greensheet doesn't exist at the time I create the formula, i get promted to browse for a valid reference.
If I click Cancel the formula does indeed get entered and correctly returns "sheet not created yet"

However, if I then create a new sheet and name it greensheet
That formula does not adjust and show the value of C4 in greensheet.

I have to F2 and RE-Enter the formula for it to work.

 

[oldbrewer]

have you got auto calculate turned OFF ? (JONMO u know 1000 times more than me give him solution)

 

[Jonmo1]

Calculation wouldn't make a difference either way.

I can only think of 2 ways to reference sheets that doesn't exist yet.
1) With Indirect
=INDIRECT("SomeSheetName!A1")
Once SomeSheetName is created, that formula will still error until a calculation is triggered.

2) With VBA code.

If what you're really wanting is to refer to the 6th sheet
Here's a UDF
Paste this code into a new VBA module

Public Function NthSheet(sh As Long, c As Range)
Application.Volatile
NthSheet = Sheets(sh).Range(c.Address)
End Function

Then you can use it like
=NthSheet(6,G5)

That will return the value of G5 in the 6th sheet.

Again, if the 6th sheet doesn't actually exist, it will be an error value.
And it won't recalculate until a calculation is triggered.

 

[tedwards867]

This video describes how to do this. As long as your new pages are created in between your summary page and the last page in the formula it will automatically be included.