Possible for Formula to Referencing Future Worksheet?

[hop17]

Does anyone know if it's possible to reference a worksheet that has not yet been created, avoiding the #REF! result?

The formula I have is correct, however the macro generates new worksheets throughout the month in which the totals sheet is referencing. The problem being that the totals sheet is referencing worksheets not yet created.

Hoping someone knows a work-around....thanks!

 

[taigovinda]

Could you post one of the formulas that you want to fix?

 

[lenze]

Hi and welcome to the Board!!
Here's a Workaround
Name new first sheet named Start and a new last Sheet named Last. Now refer to your Totals like
=-SUM(First!A1:Last!A1). Adjust your macro to insert your new sheets before Last.
Oh, then you can hide First and Last!!

lenze

 

[hop17]

Sure...

So here is the formula, which works correctly and is referencing worksheet "01" (for the first day of the month).

=IF(ISERROR(VLOOKUP(A6,'01'!$B$68:$K$128,10,FALSE)),"",(VLOOKUP(A6,'01'!$B$68:$K$128,10,FALSE))

Below is what I have for worksheet "02" because the "02" worksheet has not yet been created by the macro

=IF(ISERROR(VLOOKUP(A6,#REF!$B$6:$K$30,10,FALSE)),"",(VLOOKUP(A6,#REF!$B$6:$K$30,10,FALSE)))

Once I run the macro, "02" will be in the worksheet, however the formula will not work, unless I go back to it and enter "02" where I'm getting #REF!.

 

[hop17]

Hi Lenze,

Thanks for the welcome. In the post above, I show the formula I'm working with. Would you be so kind as to tweak my formula to what you are specifying with the START and END worksheets. I think I follow, but would be very helpful to see.

Thanks!

 

[pgc01]

Hi

You can use Indirect()

=IF(ISERROR(VLOOKUP(A6,INDIRECT("'02'!$B$6:$K$30"),10,FALSE)),"",(VLOOKUP(A6,INDIRECT("'02'!$B$6:$K$30"),10,FALSE)))

 

[lenze]

I'm not sure that approach will work for what you are doing. Could you not write your formula for non-existing sheets, but have your macro add the formula after it creates the new sheet??

lenze

 

[taigovinda]

Hi

You can use Indirect()

=IF(ISERROR(VLOOKUP(A6,INDIRECT("'02'!$B$6:$K$30"),10,FALSE)),"",(VLOOKUP(A6,INDIRECT("'02'!$B$6:$K$30"),10,FALSE)))

You can also use a cell reference in the place of '02' if that helps in the creation of the formulas...

 

[hop17]

Thanks for the feedback. I've tried the indirect function, but was still getting the #REF! error.

Also, in terms of macro, right now the macro takes the data from a sheet, copies and pastes (numbers only, no formulas) to a new worksheet and renames the new workbook by date (01,02,03 etc...)

Open to any work-arounds with the macro, I'm just not that saavy with VB. For instance, would I have the macro create the new worksheet, rename it and then also enter a formula on the totals tab after the worksheet exists? If I go that route, how will the macro know where the formula should go on future days with different worksheet names?

 

[lenze]

OK, can you post your macro and tell us where you want the formulas to go on the Totals Sheet? In a column, Every 5th Row? Certain Cells related to the sheet name?

lenze