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.