Reference a sheet that might not exist

[getitdaily]

I'm trying to reference one cell to a cell in another sheet that might not exist. Anyway to do that?

 

[Hasit]

Yes you can, but you will get a #REF! in the cell.

Just a quick question - why would you want to reference a non-existent sheet?

 

[getitdaily]

I have a couple of workbooks our accounting dept enters info into manually once a month. I am trying to eliminate the manual process with macros that input that data from the reports they key off of. In the first workbook I create a new sheet which the accounting department will rename after they run the macro. The second workbook references the newly created sheet, each month. IE, book 1 sheet1 will be renamed to October03, the next month sheet1 will be renamed November03 and so on.

In book2 I can't tell the macro to look at October03 because it needs to look at Noveber03 if it's November,etc. I can't tell it to look at sheet1 of book1 because sheet1 of book1 won't exist until the macro is ran. If I reference sheet1 that doesn't exist the formula is forever messed up, #REF.

I have a layout sheet that has all the formulas in both books. I might just have to do all the formulas in the macro for book2.

 

[beniez]

Hi

Use ISERROR, as in:

=IF(ISERROR('Missing'!B12),"Sheet missing", 'Missing'!B12)

(Where 'Missing' is the name of the sheet that might be missing)

If you put this in while the sheet is missing, Excel will open a files dialog box to allow you to find it; press cancel unless you want to do this.

HTH

Ben