Ref another page

[H_Warburton]

I know I am reaching on this one, but does anyone know if you can ref a cell in a worksheet that has yet to be created. I am building a template. My first page is a summary of the follow on calculation pages, but I can not predict how many calculation pages there will be. I will be making copies of the first calculation page as needed and referancing their results on the summary sheet.

SUMMARY = ref cell P25 of CAL, CAL (2), CAL (3), ect.
CAL = make copies as nessary
CAL (2)
CAL (3)
etc.

 

[Andrew Poulsom]

Insert a sheet before your summary sheet and name it First. Insert another sheet after your CAL sheet and name it Last. Then use a formula like:

=SUM(First:Last!A1)

When you copy the CAL sheet ensure that the copy goes before sheet Last.

 

[H_Warburton]

I do not want to sum the P25 cells, it is more of a grouping and display.
The summary sheet would look something like this
CAL = 17
CAL (2) = 19
CAL (3) = 16

This type ype of set up, but I want that 17, 19, 16 to be refs from; CAL cell P25, CAL (2) cell P25, CAL (3) cell P25

 

[Andrew Poulsom]

You can enter a formula like:

='CAL (2)'!A1

but if the sheet doesn't exist you will be prompted with an Update Values dialog and if you press Cancel it will return #REF!. However it will work if the sheet is added later.

 

[H_Warburton]

I have that and I am getting the REF error, but it does not update when the sheet is created. Do I need any special settings?

 

[Andrew Poulsom]

You need to press F2 Enter to update it after the sheet is inserted.

 

[H_Warburton]

Thank you very much! You guys have saved me again!