help referencing non existant tabs in worksheet

[ross3093]

I want to make a worksheet reference to a tab that will be imported later....
I though I could to it by referencing a tab within the worksheet with the same formating of the future tab I would be importing....
then...
deleteing that tab and saving the worksheet.
However when I did so excell deleted all my formulas and put in the '#ref' erorr....
can I use an if then statement and do something like if tab exists....

[royUK]

The formulas aren't deleted - the #Ref error is reporting that the formulas aren't able to reference the sheet. Try adding the sheet then check the formulas

[DonkeyOte]

In the most basic form:

=IF(ISREF(INDIRECT("'Sheet2'!A1")),INDIRECT("'Sheet2'!A1"),"")

the above will return Null until such time as Sheet2 exists at which point it will return contents of Sheet2!A1

note: INDIRECT is Volatile - see link for more info.

[ross3093]

In the most basic form:

=IF(ISREF(INDIRECT("'Sheet2'!A1")),INDIRECT("'Sheet2'!A1"),"")

the above will return Null until such time as Sheet2 exists at which point it will return contents of Sheet2!A1

note: INDIRECT is Volatile - see link for more info.

Great that works, I just couldnt remember the code...been out of school for only 5 months....
=IF(ISREF(INDIRECT("'sheet2'!A14"));INDIRECT("'sheet2'!A14");"")

is what I used.. for 2007 excell
>But I have a list of like 100 cells and I am having trouble auto filling by dragging the corner of the cell highlighted to copy the formula
EG Cell 1
=IF(ISREF(INDIRECT("'SHEETXXX''!A14"));INDIRECT("'SHEETXXX'!A14");"")
EG Cell2
=IF(ISREF(INDIRECT("'SHEETXXX'!A15"));INDIRECT("'SHEETXXX'!A15");"")
EG Cell3
=IF(ISREF(INDIRECT("'SHEETXXX'!A16"));INDIRECT("'SHEETXXX'!A16");"")
EG Cell n+1...
=IF(ISREF(INDIRECT("'SHEETXXX'!A1(N+1)..."));INDIRECT("'SHEETXXX'!A(N+1)...");"")

[DonkeyOte]

In which cell does the first formula reside ?

see post from earlier today which highlights the use of R1C1 notation with INDIRECT (for sake of auto fill)

[ross3093]

In which cell does the first formula reside ?

see post from earlier today which highlights the use of R1C1 notation with INDIRECT (for sake of auto fill)

B38 in a another tab labeled LEAD wksht

[DonkeyOte]

OK so assume LEAD!B38, B39, C38 are to reference SheetXXX!A14, A15, B14 etc then using R1C1

LEAD!B38
=IF(ISREF(INDIRECT("'SheetXXX'!$A$1")),INDIRECT("'SheetXXX'!R[-24]C[-1]",FALSE),"")
copied down and right etc as nec.

Note the first INDIRECT can always reference A1 - it's just checking to see if the sheet exists in the first instance.

If you want to use A1 notation you can and is simple enough if you need only fill vertically, eg:

LEAD!B38
=IF(ISREF(INDIRECT("'SheetXXX'!$A$1")),INDIRECT("'SheetXXX'!A"&14+ROWS(B$38:B38)-1),"")