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").offs et(0,3).value

So you wouldn't even need that extra name.



InfinityDesigns wrote:

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

--

Dave Peterson

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.

"Dave Peterson" wrote:

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").offs et(0,3).value

So you wouldn't even need that extra name.



InfinityDesigns wrote:

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

--

Dave Peterson

I don't think I understand enough to help.

You want to try one more time?

InfinityDesigns wrote:

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.

"Dave Peterson" wrote:

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").offs et(0,3).value

So you wouldn't even need that extra name.



InfinityDesigns wrote:

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

--

Dave Peterson


--

Dave Peterson

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"

I thought that the OP put:

=HollywoodHills

in the cell (an actual formula).

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"

--

Dave Peterson

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!
"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"

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"