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!

 

[hop17]

Thanks again for the help!

The only relevant part of the macro is:

Sheets("CR Journal").Copy After:=Sheets(Worksheets.Count)
Cells.Copy
Cells.PasteSpecial xlPasteValues

ActiveSheet.Name = Range("B1").Value

My goal is pretty straight foward. I have a list of cashiers from column B6:B30, and a list of their drop variances in column K6:K30. The variances are tracked for each day of the month in order to obtain a cumulative total. The order of the cashiers is always changing, so that's why I use a VLOOKUP by cashier ID and then pull the variance from column K to track. Everything works find, it's just that since this is a running total, the formula won't work since the worksheet does not yet exist for the future dates.

 

[hop17]

Are attachments allowed on here?

The specifics of the totals sheet are a list of names from A6:A60. And day one of their respective variances in column C going 31 columns over, for each day of the month. So each cell is matching the row name with the column date to get the specific variance for each individual day of the month.