Okay Excel experts

The question I have is this, 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

Seems a very difficult way to price things when a simple vlookup would
provide your pricelist, but you should be able to use a formula much more
quickly than a macro. The formula would be something like =A1&"C"

If "HollywoodHills" were in A1, this would return HollywoodHillsC to the
cell in which the formula resides. You can then copy the cell(s) with this
formula, and Edit-Paste Special, Values.

But I think you should have used a vlookup to perform this function.
http://www.officearticles.com/excel/...soft_excel.htm

*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"InfinityDesigns" <InfinityDesigns@discussions.microsoft.com> wrote in
message news:A4C428B8-C0D0-4668-9B53-C672C97E47F9@microsoft.com...
> The question I have is this, 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
>
>

You have a response at your other thread, too.

InfinityDesigns wrote:
>
> The question I have is this, 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

And, PLEASE try to use meaningful subject lines and show a bit of patience.

--
Don Guillett
SalesAid Software
donaldb@281.com
"InfinityDesigns" <InfinityDesigns@discussions.microsoft.com> wrote in
message news:A4C428B8-C0D0-4668-9B53-C672C97E47F9@microsoft.com...
> The question I have is this, 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
>
>

Thank you for responding. I am sure that there are easier ways of doing a
lot of the things I am doing in Excel. I am fairly new to it and 100% self
taught. I did try the formula you gave me but it returned the wrong value.
When I type =HollywoodHills in cell K10 that defined name returns a value of
$34.56 that the name got from a different worksheet. Then I used your
formula in cell M10; =K10&"C". When I do this, it returns a value of 34.46C
in cell M10. Instead what I want it to return is the value that would be
returned if I had typed in M10 =HollywoodHillsC which would return a value of
$20.25. I hope I am making this clear because I could use all the help I can
get regarding this aspect.
"Anne Troy" wrote:

> Seems a very difficult way to price things when a simple vlookup would
> provide your pricelist, but you should be able to use a formula much more
> quickly than a macro. The formula would be something like =A1&"C"
>
> If "HollywoodHills" were in A1, this would return HollywoodHillsC to the
> cell in which the formula resides. You can then copy the cell(s) with this
> formula, and Edit-Paste Special, Values.
>
> But I think you should have used a vlookup to perform this function.
> http://www.officearticles.com/excel/...soft_excel.htm
>
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "InfinityDesigns" <InfinityDesigns@discussions.microsoft.com> wrote in
> message news:A4C428B8-C0D0-4668-9B53-C672C97E47F9@microsoft.com...
> > The question I have is this, 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
> >
> >
>
>
>

You have a lot of threads going. Let's drop the previous one and stay here.

I think you can do this if you use a UserDefinedFunction to get the formula out
of the first cell.

I use a UDF like this to return the formula from a cell:

Option Explicit
Function GetFormula(Rng As Range)
Dim myFormula As String
GetFormula = ""
With Rng.Cells(1)
If .HasFormula Then
If Application.ReferenceStyle = xlA1 Then
myFormula = .Formula
Else
myFormula = .FormulaR1C1
End If
If .HasArray Then
GetFormula = "{=" & Mid(myFormula, 2, Len(myFormula)) & "}"
Else
GetFormula = myFormula
End If
End If
End With
End Function

So if I had that UDF available, I could put:

=HollywoodHills
in A1.

Then I could use this:
=INDIRECT(MID(getformula(A2),2,255)&"c")
To get the value from HollyWoodHillsC

Seems like a lot of work to just not have to type that to me, though.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=getformula(A1)
to test it out.

Then try the longer formula to see if it works the way you want.


InfinityDesigns wrote:
>
> Thank you for responding. I am sure that there are easier ways of doing a
> lot of the things I am doing in Excel. I am fairly new to it and 100% self
> taught. I did try the formula you gave me but it returned the wrong value.
> When I type =HollywoodHills in cell K10 that defined name returns a value of
> $34.56 that the name got from a different worksheet. Then I used your
> formula in cell M10; =K10&"C". When I do this, it returns a value of 34.46C
> in cell M10. Instead what I want it to return is the value that would be
> returned if I had typed in M10 =HollywoodHillsC which would return a value of
> $20.25. I hope I am making this clear because I could use all the help I can
> get regarding this aspect.
> "Anne Troy" wrote:
>
> > Seems a very difficult way to price things when a simple vlookup would
> > provide your pricelist, but you should be able to use a formula much more
> > quickly than a macro. The formula would be something like =A1&"C"
> >
> > If "HollywoodHills" were in A1, this would return HollywoodHillsC to the
> > cell in which the formula resides. You can then copy the cell(s) with this
> > formula, and Edit-Paste Special, Values.
> >
> > But I think you should have used a vlookup to perform this function.
> > http://www.officearticles.com/excel/...soft_excel.htm
> >
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> > www.MyExpertsOnline.com
> >
> >
> > "InfinityDesigns" <InfinityDesigns@discussions.microsoft.com> wrote in
> > message news:A4C428B8-C0D0-4668-9B53-C672C97E47F9@microsoft.com...
> > > The question I have is this, 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