HELLLP!!!! This should be fairly simple I would think!

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

Guest

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
 
I would guess that whole sale price field would be in the same relative position
to the retail price--maybe always 3 columns to the right???

If that's true, you can use a worksheet formula to get that cell:

=offset(hollywoodhills,0,3)

In VBA, you could do this:

msgbox worksheets("mySheet").range("hollywoodhills").offset(0,3).value

So you wouldn't even need that extra name.
 
Thank you for your response. I am fairly new to Excel. I understand how the
offset works but the name HollywoodHills is only an example. The name will
change for each product line on the work order. When I type in
=HollywoodHills in K10 it gives me a return value of $34.56 which is my
retail price that the name HollywoodHills got from a different worksheet.
What I am trying to do is take whatever "name" is used in cell K10, take that
same name, whatever it may be, place the value from that same name in cell
N10 and add the "C" to the name in order to return the wholesale price of the
product that is in cell K10. Thank you so much for your help.
 
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
Click to expand...

If A1 contains "HollywoodHills", the following formula will return the
value of the name "HollywoodHillsC":

=INDIRECT(A1&"C")
 
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!
 
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!
Click to expand...

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")
 
Back
Top