Formula to print sheet name does not work using Excel Template

[Rob89]

Hi there,

I'm having problems with a formula I've found which finds and prints a specific Sheet name from the filename data. It works whenever the file is saved as a spreadsheet (.xlsx or .xls) but not as an Excel template (.xltx). (It displays a #VALUE! error.)

I can pull in all the data I need and nothing will happen until I save the file as a spreadsheet. I'm guessing this has something to do with the way the formula works - it's looking for the filename, which technically doesn't exist as the working file is a template. When the file is saved as a spreadsheet, a filename is created and the data comes through.

Here's the formula:

=RIGHT(CELL("filename",Step1_Print_Report!A1),LEN(CELL("filename",Step1_Print_Report!A1))-FIND("]",CELL("filename",Step1_Print_Report!A1),1))

Step1_Print_Report is the name of the sheet I'm trying to identify, as this is where the data is stored. Is there a similar formula I could try that would work even when the file has not been saved as a spreadsheet (i.e. exists in template mode still?)

Background:

To explain why I've done this. I've built a custom Email Reporting template for use with our email marketing software to enable custom reporting. It pulls together data from various sheets and presents it nicely.

As some of the data copy/pasted from the web appears in different rows per report, I've created a template that looks for certain data dynamically i.e. it looks for where a heading is and then adds the correct number to the row reference to calculate where the corresponding value is held.

Really, I was just trying to future proof the template - should I ever need to reorder or rename the sheets, Excel would accommodate the changes for me and would save me having to update about 30 odd formulas!

Cheers,

Rob

 

[BrianMH]

=IFERROR(RIGHT(CELL("filename",Step1_Print_Report!A1),LEN(CELL("filename",Step1_Print_Report!A1))-FIND("]",CELL("filename",Step1_Print_Report!A1),1)),RIGHT(CELL("address",Step1_Print_Report!A1),LEN(CELL("address",Step1_Print_Report!A1))-FIND("]",CELL("address",Step1_Print_Report!A1),1)))

This will do what you want but I'm curious if you already know the sheet name and that is all you are returning why use a formula to obtain it? Changing the sheet order won't change anything only changing the sheet name and formulas update when you change the sheet name anyway.

 

[Rob89]

=IFERROR(RIGHT(CELL("filename",Step1_Print_Report!A1),LEN(CELL("filename",Step1_Print_Report!A1))-FIND("]",CELL("filename",Step1_Print_Report!A1),1)),RIGHT(CELL("address",Step1_Print_Report!A1),LEN(CELL("address",Step1_Print_Report!A1))-FIND("]",CELL("address",Step1_Print_Report!A1),1)))

This will do what you want but I'm curious if you already know the sheet name and that is all you are returning why use a formula to obtain it? Changing the sheet order won't change anything only changing the sheet name and formulas update when you change the sheet name anyway.

Thanks BrianMH - I'll give that a go now.

Yep, I see your point, I forgot to say that I'm using INDIRECT formulas with CONCATENATE for this template. So Excel looks for the headings and returns the row it's found in, then I use CONCATENATE to create a text string which is, in effect, the path to the data. Then I use a series of INDIRECT formulas to reference the text string I created.

As the template is setup this way, the only other way to get the sheet name in there would be 'hard code' in there as a text string, so I'd have to manually edit 30 odd formulas.

Yep, it's long winded, but it works!

Will post back shortly.

 

[Rob89]

Just tried your formula, BrianMH. It seems to have fixed the issue of not working when the file is running as a template. However, it's making the cell references absolute by adding the cell reference A1 (actually absolutely with dollar signs), which means that the formulas are not updating correctly.

[TABLE="width: 500"]
<tbody>[TR]
[TD]New formula sheet name:
[/TD]
[TD]Step1_Print_Report!$A$1[/TD]
[/TR]
[TR]
[TD]Needed formula sheet name:
[/TD]
[TD]Step1_Print_report![/TD]
[/TR]
</tbody>[/TABLE]

Is there a way I can remove the cell reference $A$1?

Thanks,

Rob

 

[Rob89]

Just tried your formula, BrianMH. It seems to have fixed the issue of not working when the file is running as a template. However, it's making the cell references absolute by adding the cell reference A1 (actually absolutely with dollar signs), which means that the formulas are not updating correctly.

[TABLE="width: 500"]
<tbody>[TR]
[TD]New formula sheet name:[/TD]
[TD]Step1_Print_Report!$A$1[/TD]
[/TR]
[TR]
[TD]Needed formula sheet name:[/TD]
[TD]Step1_Print_report![/TD]
[/TR]
</tbody>[/TABLE]

Is there a way I can remove the cell reference $A$1?

Thanks,

Rob

I have just tested and I can use your formula in one cell and then a LEFT (LEN) formula to trim 5 characters from the string to remove the !$A$1. This does then make the template work in template mode, however, it then unfortunately breaks when I save it! I might still be able to use your formula, BrianMH, if it's amended to remove the two step process.

Thanks again for your help

Rob