On Sat, 18 Jun 2005 08:23:03 -0700, InfinityDesigns
wrote in
microsoft.public.excel.misc:
Thank you for your help. I used the formula you suggested
=INDIRECT(A1&"C")and it retured #REF!. You said if the cell A1 contains
"HollywoodHills" This formula would return the value of the name
"HollywoodHillsC". The cell does not contain HollywoodHills, HollywoodHills
is a defined name that returns a value (retail price)from another worksheet.
Same with HollywoodHillsC, that returns another value from the same worksheet
as HollywoodHills except the "C" at the end of the name returns another value
(wholesale price). I hope I am making this clear. Example: If I go to cell
K10 and type =HollywoodHills that returns the retail price of $34.56, then I
want =HollywoodHillsC, which returns a value of $20.25, the wholesale price,
to go into cell N10. I need a formula to add the "C" to the name in K10
because some people that will be using the work order should not have access
to wholesale pricing. Thank you for your help!
So you need the text of the formula which is in K10 to be used in N10.
AFAIK there is no Excel worksheet function to do that. You need to
introduce the following User Defined Function:
Function GetFormulaText(Cell As Range) As String
GetFormulaText = Mid(Cell.Formula, 2)
End Function
Then you can put this in N10:
=INDIRECT(GetFormulaText(K10)&"C")
"Michael Bednarek" wrote:
On Thu, 16 Jun 2005 20:07:02 -0700, InfinityDesigns
wrote in
microsoft.public.excel.misc:
The question I have is can you add to an existing formula in a particular
cell. Example: I have a workbook where I fill out a work order for jobs
sold. In that workbook I have defined many names for individual products in
several other workbooks to return a retail price and a wholesale price. The
defined name for the retail price is the name of the product ie;
"HollywoodHills", the defined name for the wholesale price is the same but
with a "C" at the end ie; "HollywoodHillsC". I am wanting to have a macro to
add the "C" to the end of the defined name in a cell to return the wholesale
price of the product. This would greatly uncomplicate my life and any help
is greatly appreciated
If A1 contains "HollywoodHills", the following formula will return the
value of the name "HollywoodHillsC":
=INDIRECT(A1&"C")
--
Michael Bednarek
http://mbednarek.com/ "POST NO BILLS"