Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
HOW TO NAME A FORMULA ON EXCEL 1
- Thread starter zoed44
- Start date
AnotherHiggins
Technical User
What?
[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
- Thread starter
- #3
From the Microsoft help file:
Create a name to represent a formula or a constant
Show All
Hide All
On the Insert menu, point to Name, and then click Define.
In the Names in workbook box, enter the name for the formula.
In the Refers to box, type = (equal sign), followed by the formula or the constant (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.) value.
Create a name to represent a formula or a constant
Show All
Hide All
On the Insert menu, point to Name, and then click Define.
In the Names in workbook box, enter the name for the formula.
In the Refers to box, type = (equal sign), followed by the formula or the constant (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.) value.
AnotherHiggins
Technical User
I've honestly never thought about using Names to store functions, pbrodsky. Clever. (Even if it is right there in Excel help.)
zoed44: The big caveat with that approach is that you won't be able to plug in cell references at will. But if your formula always uses, say, the cell to the left of the cell containing the formula, you can use [F4] on cell references to remove the "$"s, changing the references to relative. This would enable you to use your Named Formula in different rows/columns. But it would still only ever refer to the cell to the left of where you type the name.
For a more robust solution, you might want to look at UDFs (User Defined Functions) in VBA. That would allow you to do whatever you need in VBA behind the scenes and assign any name you wish to the formula.
If you want to go that route, please create a new thread in forum707
[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
zoed44: The big caveat with that approach is that you won't be able to plug in cell references at will. But if your formula always uses, say, the cell to the left of the cell containing the formula, you can use [F4] on cell references to remove the "$"s, changing the references to relative. This would enable you to use your Named Formula in different rows/columns. But it would still only ever refer to the cell to the left of where you type the name.
For a more robust solution, you might want to look at UDFs (User Defined Functions) in VBA. That would allow you to do whatever you need in VBA behind the scenes and assign any name you wish to the formula.
If you want to go that route, please create a new thread in forum707
[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
AnotherHiggins
Technical User
Slowpoke ![[wink]](/data/assets/smilies/wink.gif "[wink] [wink]")
[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]
Help us help you. Please read FAQ 181-2886 before posting.
I tried to name a formula a while back. Worked ok until I tried to use it on another sheet in the workbook.
I wanted to type my range name into a cell and get a result equal to the value in the cell to the left of the formula * LGSup (a named range holding a constant)
I used the approach suggested by Pbodsky.
(With cell c26 selected) In the refers to box I typed:
=ROUND(B26*LGSup,0)
Trouble is when I go back into Insert,Names Define it shows as
=ROUND([red]Sheet3![/red]B26*LGSup,0)
The named formula worked anywhere on sheet3 but not on another sheet. Really confusing. Gave up.
Suggest you do use UDF or split the formula into 2 or post it here so someone can simplify it.
Good Luck!
Gavin
I wanted to type my range name into a cell and get a result equal to the value in the cell to the left of the formula * LGSup (a named range holding a constant)
I used the approach suggested by Pbodsky.
(With cell c26 selected) In the refers to box I typed:
=ROUND(B26*LGSup,0)
Trouble is when I go back into Insert,Names Define it shows as
=ROUND([red]Sheet3![/red]B26*LGSup,0)
The named formula worked anywhere on sheet3 but not on another sheet. Really confusing. Gave up.
Suggest you do use UDF or split the formula into 2 or post it here so someone can simplify it.
Good Luck!
Gavin
-
1
- #13
You can assign a NAME that will apply to the entire workbook; and there is also a way to assign a NAME on a Sheet by Sheet basis -- depending on the worksheet author's needs.
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
Hi Yogi,
I fully understand what you are saying. However the issue is the actual formula that appears in the refers to area.
What happens in my example above is that I have named a formula. The formula name is, say, MyFormulaName.
If on sheet3, cell B3 I enter =MyFormulaName I get the expected result (sheet3!A3*my constant LGSup rounded to zero decimal places).
If on sheet3, cell c4 I enter =MyFormulaName I again get the expected result (sheet3!B4*my constant LGSup rounded to zero decimal places).
However if I do the same on sheet2 the result is not
sheet[red]2[/red]!A3*my constant LGSup rounded to zero decimal places but uses instead the value from sheet3!A3.
The reason seems to be that when you name a formula references to cells MUST specify the sheet.
Gavin
I fully understand what you are saying. However the issue is the actual formula that appears in the refers to area.
What happens in my example above is that I have named a formula. The formula name is, say, MyFormulaName.
If on sheet3, cell B3 I enter =MyFormulaName I get the expected result (sheet3!A3*my constant LGSup rounded to zero decimal places).
If on sheet3, cell c4 I enter =MyFormulaName I again get the expected result (sheet3!B4*my constant LGSup rounded to zero decimal places).
However if I do the same on sheet2 the result is not
sheet[red]2[/red]!A3*my constant LGSup rounded to zero decimal places but uses instead the value from sheet3!A3.
The reason seems to be that when you name a formula references to cells MUST specify the sheet.
Gavin
Hi Gavin:
MyFormulaName written as Sheet3!A1 will always use cell values assigned to MyFormulaName in Sheet3.
If I want the formula to use associated values in the corresponding cell of a Sheet where the formula is written, then I can use the formula
MyFormulaName1 written as !A1
(without preceding the ! by a specific Sheet number)
I hope this helps.
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
You Are Very Welcome Gavin ... now let us keep EXCELing!
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
PeterMoran
MIS
Hi,
In my spreadsheet template I have the following NAMED formula:
=INDIRECT("R[-1]C",FALSE)
named as CellAbove, which I use in the SUM function eg:
=SUM(A3:CellAbove)
This means it is IMPOSSIBLE to insert a row above the SUM line without it being included in the total. I'm not sure about Excel 07, but this was certainly handy in earlier versions of Excel.
Also to assign a name just for a sheet I think you just enter the sheetname followed by the Name (eg: Sheet1.CellAbove ).
Good Luck!
Peter Moran
In my spreadsheet template I have the following NAMED formula:
=INDIRECT("R[-1]C",FALSE)
named as CellAbove, which I use in the SUM function eg:
=SUM(A3:CellAbove)
This means it is IMPOSSIBLE to insert a row above the SUM line without it being included in the total. I'm not sure about Excel 07, but this was certainly handy in earlier versions of Excel.
Also to assign a name just for a sheet I think you just enter the sheetname followed by the Name (eg: Sheet1.CellAbove ).
Good Luck!
Peter Moran
A named range is really a named formula: See John Walk's article here:
and there are many splendid examples on Steven Bullen's Excel page, especially this one:
Chris
Don't count the days, make the days count
Muhammad Ali
and there are many splendid examples on Steven Bullen's Excel page, especially this one:
Chris
Don't count the days, make the days count
Muhammad Ali
- Status
Similar threads
- Locked
- Question