Calling for pages before they are created.

B

bsspewer

Here's my dilemma. I want to create one sheet as a summary sheet that
pulls data from specific cells that are in other pages. The problem is
that some of these pages aren't created yet because I create one new
page each day. It's alot of work to go back to that first page and
type in the exact same info as the day before, just changing the name
of the sheet to the next day.

Example: "='2006.02.01'!$Z$2" is for feb 1st, and the cell following
states "='2006.02.02'!$Z$2" for feb 2nd. Right now, I have to retype
it each day, because if I try calling for a page before the page is
made, excel gets confused. If anyone follows please help out.
 
C

CaptainQuattro

Since you sheets are named according to the date, you could try the
following:

=if(today()<date(2006,4,15),"",2006.4.15!$Z$2)
 
D

Dave Peterson

You can use =indirect() to check to see if that sheet is there:

=IF(ISERROR(CELL("address",INDIRECT("'2006.02.01'!a1"))),
"Not there yet",INDIRECT("'2006.02.01'!$Z$2"))

(all one cell)
 
B

bsspewer

Alright dave, we're getting there now. I can now go ahead of the day
and type up all the cells having them refer to pages that are uncreated
yet. This lightens up the day to day work, but I still have to manually
change each sheet name in the arguments (2006.02.01 to 2006.02.02).

Is there a way I can have it call for the name of a sheet using a
cell?

Example: cell B2 says 2006.02.02 and in your formula I put something
like "'B2'"! thereby it uses what I've typed in cell B2 as the name of
the sheet it looks for. This prevents me from manually typing each page
name, and can refer to a cell much easier.
 
D

Dave Peterson

=IF(ISERROR(CELL("address",INDIRECT("'"&TEXT(B2,"yyyy.mm.dd")&"'!a1"))),
"Not there yet",INDIRECT("'"&TEXT(B2,"yyyy.mm.dd")&"'!$Z$2"))
 
Top