How do I use a worksheet name as an input variable to a formula?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to use the name of a worksheet, say "postage", as an input variable
in a formula on that worksheet.

For example, assume that I have a worksheet named "Postage", and assume that
'sheetName' is a variable containing the name of the worksheet.

I would want to use this formula: ="Costs of "&sheetName&":"
to create this line of text: 'Costs of Postage:'

Does someone know how to do that, or if it's even possible (preferably
without macros).
 
One way is to use INDIRECT with this Harlan-inspired technique to return the
active sheetname ..

Note: Workbook must be saved first

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in any
sheet. It will auto-extract the sheetname implicitly.

Eg, if you enter: =WSN in any sheet, any cell, it'll return the sheetname in
that cell.

For your eg, you would use: ="Costs of "&WSN&":"
 
Define it in a name. Here is how:
1. If the file is not saved yet, then save it now
2. (xl2003) Insert, Name, Define
(xl2007) Formulas, Define Name
3. Make sure the scope is Workbook
4. In the refers to box, paste this:
=MID(CELL("filename",Postage!$A$1),FIND("]",CELL("filename",Postage!$A$1))+1,99)
 
Max, I assume he wants WSN to refer to a definite Worksheet in his Book
INDIRECT("A1") has to be replaced with SheetName!A1

Max said:
One way is to use INDIRECT with this Harlan-inspired technique to return the
active sheetname ..

Note: Workbook must be saved first

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in any
sheet. It will auto-extract the sheetname implicitly.

Eg, if you enter: =WSN in any sheet, any cell, it'll return the sheetname in
that cell.

For your eg, you would use: ="Costs of "&WSN&":"

---
tonymotion said:
I'd like to use the name of a worksheet, say "postage", as an input variable
in a formula on that worksheet.

For example, assume that I have a worksheet named "Postage", and assume that
'sheetName' is a variable containing the name of the worksheet.

I would want to use this formula: ="Costs of "&sheetName&":"
to create this line of text: 'Costs of Postage:'

Does someone know how to do that, or if it's even possible (preferably
without macros).
Click to expand...
Click to expand...
 
Max,

That worked perfectly!

You and Tevuna both gave me what I asked for, but what I really needed was
the ability to set up the exact same equation for all sheets in a workbook.
So Max's extra modification did that.

The magic line that I was missing was: CELL("Filename",INDIRECT("A1"))
Once I saw that this gives me the full path to the current worksheet as a
character string, I realized that I could take that apart in exactly the way
that both of you two recommended. Then all I needed was a simple FIND and
MID, and that was it.

Thanks again! You guys rock!

-Tony
 
Back
Top