Possible for Formula to Referencing Future Worksheet?

hop17

New Member
Joined
Aug 19, 2009
Messages
12
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!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
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

New Member
Joined
Aug 19, 2009
Messages
12
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

New Member
Joined
Aug 19, 2009
Messages
12

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,885
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

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

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

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
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

New Member
Joined
Aug 19, 2009
Messages
12
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

Forum statistics

Threads
1,144,568
Messages
5,725,046
Members
422,590
Latest member
Mikeyyy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top