INDIRECT with Variable Sheet Name

[dwgnome]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o></o>
In Sheet 01, cell C58 holds the month (as text), D58 holds the day number (as extracted from sheet name), and E58 holds the year (as number). I have other cells on this sheet that reference these three cells and combine them into one usable date. All other sheets named 02 through 31 reference these cells in simple fashion like:<o></o>
<o></o>
C58 ='01'!$C$58 for month; D58 ='01'!$D$58 for month and E58 ='01'!$E$58 for year.<o></o>
<o></o>

Now I would like to be able to modify to be able to use another sheet with similar setup in case Sheet 01 is deleted. I would like the cells on worksheets 02 through 31 to have the ability to point to a different worksheet with a name like JUL 10 SUM having in cells C102, D102 and E102 set up similar to Sheet 02.
<o></o>
<o></o>
Normally I would do ='JUL 10 SUM'!C102 which would grab the month (i.e, JULY) and ='JUL 10 SUM'!D102 would grab the day, ='JUL 10 SUM'!E102 for the year. The problem is when I work on a new month or year worksheet, I rename the tab from JUL 10 SUM to AUG 10 SUM or JAN 11 SUM depending on the month from which the data is collected. So I need a way to have the formula in Sheet 02 through 31 be able to adjust it's reference pointing back to JUL 10 SUM worksheet.
<o></o>
<o></o>
So in Sheet 02 I want go from:<o></o>
<o></o>
='01'!$C$58<o></o>
<o></o>

To something like this:<o></o>
<o></o>
=IF(ISERROR('01'!$C$58),INDIRECT("'JUL 10 SUM'!$C$102"),'01'!$C$58) and do the same for the day and year.<o></o>
<o></o>

However, I need the JUL 10 to be able to change to AUG 10 SUM etc and have the formula adjust automatically to the new name.<o></o>
<o></o>
I thought about just capturing the worksheet name in C107 of the JUL 10 SUM worksheet using:
<o></o>
<o></o>
<o>

[COLOR=black][FONT=Verdana]=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)<o:p></o:p>[/FONT][/COLOR]

</o>
<o></o>

and then on Sheet 02 cell C65, reference C107 which shows up as ='JUL 10 SUM'!C107, now I need to isolate the JUL 10 SUM portion but I can't seem to extract the text between the apostrophes to isolate only the worksheet name.
<o></o>
<o></o>
I tried this formula but it results in blank.
<o></o>
<o></o>
<o>

[COLOR=black][FONT=Verdana]=IF(ISERROR(FIND("'",C65)),"",IF(ISERROR(FIND("'",C65)),"",LEFT(RIGHT(C65,LEN(C65)-FIND("'",C65)),FIND("'",RIGHT(C65,LEN(C65)-FIND("'",C65)))-1)))<o:p></o:p>[/FONT][/COLOR]

</o>
<o></o>

Once isolated I wanted to test to see if =INDIRECT("'"&C65&"'!"&C102) would work.
<o></o>
<o></o>
I need help isolating the sheet name to use in the INDIRECT portion.<o></o>

 

[AlphaFrog]

Would this get the sheet name?

In C107 of the JUL 10 SUM worksheet...
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+4,10)

It seems that all your sheet names will be just 10 characters long.

 

[dwgnome]

I tried =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+4,10) and the result came out 10 SUM. So I'm not sure what that approach accomplish since I need the entire worksheet name.

All my worksheets are numbered 01 through 31 so just two digits. The last worksheet is named JUL 10 SUM which is 10 characters long and that is the one that changes.

 

[AlphaFrog]

I thought about just capturing the worksheet name in C107 of the JUL 10 SUM worksheet using:
<o></o>

<o>

[COLOR=black]
[COLOR=black][FONT=Verdana]<o:p></o:p>[/FONT][/COLOR][FONT=Verdana]=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)<o:p></o:p>[/FONT][/COLOR]

</o>
<o></o>
and then on Sheet 02 cell C65, reference C107 which shows up as ='JUL 10 SUM'!C107

If your original formula returns ='JUL 10 SUM'!C107, then the formula I suggested should return just the JUL 10 SUM part of the text.

 

[dwgnome]

I'm not following you. On worksheet JUL 10 SUM, I use =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) in cell C107 which already gives me just JUL 10 SUM which is what I need to port over to Sheet 02 Cell C65. So what do I put in C65 or in my INDIRECT equation so that INDIRECT "sees" JUL 10 SUM as a cell reference?

 

[AlphaFrog]

I'm not following you as well. There seems to be a contradiction.

which shows up as ='JUL 10 SUM'!C107, now I need to isolate the JUL 10 SUM portion but I can't seem to extract the text between the apostrophes to isolate only the worksheet name.

I thought you had a problem isolating the worksheet name from ='JUL 10 SUM'!C107

Now you say...

I use =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) in cell C107 which already gives me just JUL 10 SUM

So if you now say you have the text already isolated then....
Say you have the text "JUL 10 SUM" in cell A1, this will use it as a sheet name reference...

=INDIRECT("'"&A1$"'$'!C107")

This will reference
'JUL 10 SUM'!C107

 

[dwgnome]

Sorry if I'm making it more complicated than it really is.

The result of =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) in cell C107 on Sheet JUL 10 SUM does result in JUL 10 SUM.

So when I change it to AUG 10 SUM the cell C107 also says AUG 10 SUM.

Now on Sheet 02 I want what is in cell C107 to appear in the following formula except where it says JUL 10 SUM it should be the name that appears in C65 (or any cell for that matter):

=IF(ISERROR('01'!$C$58),INDIRECT("'JUL 10 SUM'!$C$102"),'01'!$C$58)

something like ....INDIRECT("'"&C65&"'!"&C102).

Is that any clearer?



This way

 

[dwgnome]

Bump.

Please, can someone else help me out.

Thanks.

 

[dwgnome]

Bump.

 

[AlphaFrog]

I'd like to help, but I really don't follow what you're asking.

You say you want a formula like this...
INDIRECT("'"&C65&"'!"&C102)
...but I don't know why you don't actually use that formula.