use a formula to reference a sheet name. example... =(F3-14)!a1

[flashgordie]

Hello, is there a way to use a formula to reference a sheet name. example... =(F3-14)!a1

Where F3 is a date and by misusing 14, the resulting product would be a new date. This new date would be a sheetname from which you would reference and use cell A1

 

[SydneyGeek]

=INDIRECT("'"&F3-14&"'!A1")

Denis

 

[flashgordie]

Hi Denis, Thank you for the reply, I have taken a look at your suggestion, and believe it to be on the right path, however, I am getting a '#ref' error.

Using your formula on the template worksheet =INDIRECT("'"&F3-14&"'!j35") should take the contents of F3 which is “April-12-13” and minus 14…this should give me the sheet of March-29-13! and reference J35 on sheet ”March-29-13”… but I get the ‘#REF!’ error.

Sub-note: Just to verify my date format in F3 is intact, I have completed a simple test in and adjacent cell, and when entering =f3-14, a new date is returned “March-29-13”

I wonder if something could be wrong with the worksheet name and the way it is interpreted when pulled back into the sheet.






Here is some more background…

I have a worksheet named “template”. In cells c3, d3 and e3, I ask the user to input a date by choosing month day and year in the separate dropdowns of the aforementioned cells. Then, in cell F3, I combine each of these 3 previous inputted components using …=DATE(E3,(IF(C3<>"",MONTH("1 "&C3),"")),D3) thus giving me a date. ie: April-12-13

Sub-note: Just to verify my date formation is intact, I have tested this by using =f3-14, a new date is returned “March-29-13”

I then have a macro that creates a copy of the “template” worksheet and renames the copy using the date in F3 (of template) using the following code….

Code/
sheet_name_of_copy = Format(Sheets("Template").Range("f3").Value, "mmm-dd-yy")
/Code

It works very well and produces the new sheets as required…Currently I have 4 worksheets (template, April 26-13, April-12-13, March-29-13

Now for the issue, As mentioned…. I would like to have a formula that references the newly created worksheet.

I wonder if there could be something wrong with the workseet name and the way it is interpreted when pulled back into the sheet.

 

[flashgordie]

Just a further note on above, when I hand type =’April-12-13’!j35 it works to at least recognize the worksheet name, however I use =INDIRECT("'"&F3-14&"'!j35") and try to minus of the 14 days, it will not complete the formula. Still the error.

 

[Jonmo1]

Try
=INDIRECT("'"&TEXT(F3-14,"mmmm-dd-yy")&"'!j35")

 

[flashgordie]

We have a WINNER!!! Thank you very much. Both of you! Your assistance is greatly appreciated!

 

[Jonmo1]

Glad to help, thanks for the feedback.