Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I use a worksheet name as an input variable to a formula?
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). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I use a worksheet name as an input variable to a formula?
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) "tonymotion" wrote: 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). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I use a worksheet name as an input variable to a formula?
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&":" -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "tonymotion" wrote: 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). |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I use a worksheet name as an input variable to a formul
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" wrote: 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&":" -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "tonymotion" wrote: 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). |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I use a worksheet name as an input variable to a formul
I read it that OP wanted the sheetname returned in the same sheet, re OP's
line: I'd like to use the name of a worksheet, say "postage", as an input variable in a formula on that worksheet. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Tevuna" wrote in message ... Max, I assume he wants WSN to refer to a definite Worksheet in his Book INDIRECT("A1") has to be replaced with SheetName!A1 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I use a worksheet name as an input variable to a formul
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I use a worksheet name as an input variable to a formul
welcome, Tony.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "tonymotion" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
With QUERY how to input a variable | Excel Discussion (Misc queries) | |||
Variable input to populate data fields | Excel Discussion (Misc queries) | |||
Input Cell in One variable data table | Excel Worksheet Functions | |||
Formulas containing variable input | Excel Discussion (Misc queries) | |||
Variable Input Range for Combo Box | Excel Worksheet Functions |