Adding a character to a defined name with a formula or macro

[InfinityDesigns]

I am not sure what happened to my last thread. I was getting valuable
information from someone but my thread disappeared. What I am trying to do
is this, If I type =HollywoodHills in K10 it returns a value from that
defined name of $34.56 which is the retail price of that product. I have
another defined name of "HollywoodHillsC" which will return the value of
$20.25 which is the wholesale price of the same product. The defined names
will change with each product line on the form. So I want to be able to type
=HollywoodHills in K10 have another cell say N10 that will not be visible to
the user that will have the same name in it, and then use a formula or macro
to add the letter "C" to the end of it to return the wholesale price of
whatever product is in K10. This other cell will not be visible by the user
because some users should not have access to wholesale pricing. Can anyone
help???

[Anne Troy]

Hi. I tried to help before, but I don't know if it's "valuable". I think
you're going about it the wrong way, though. You don't need all those named
ranges; just a couple. I'm not quite sure EXACTLY what you're looking for,
but I created a workbook I hope explains what I'm suggesting. You can
download it at http://www.myexpertsonline.com/freedls/pricelist.xls. There
are no macros in it.

Note the named ranges I used, and the ranges they cover. Note the data
validation in E10, and the vlookups in E12 and E14. Note that you can hide
the sheet called PriceList.

I hope it helps!
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"InfinityDesigns" <InfinityDesigns@discussions.microsoft.com> wrote in
message news:E64AA12E-DE65-418D-9549-13819DFC5BCF@microsoft.com...
> I am not sure what happened to my last thread. I was getting valuable
> information from someone but my thread disappeared. What I am trying to
do
> is this, If I type =HollywoodHills in K10 it returns a value from that
> defined name of $34.56 which is the retail price of that product. I have
> another defined name of "HollywoodHillsC" which will return the value of
> $20.25 which is the wholesale price of the same product. The defined
names
> will change with each product line on the form. So I want to be able to
type
> =HollywoodHills in K10 have another cell say N10 that will not be visible
to
> the user that will have the same name in it, and then use a formula or
macro
> to add the letter "C" to the end of it to return the wholesale price of
> whatever product is in K10. This other cell will not be visible by the
user
> because some users should not have access to wholesale pricing. Can
anyone
> help???

[Biff]

Just thought I'd let you know that your sample file has a "bug".

Experts on line? Hope you're not charging much! <g>

<g>

Biff

"Anne Troy" <ng@officearticles.com> wrote in message
news:47bc2$42b616b4$466eb880$15447@allthenewsgroups.com...
> Hi. I tried to help before, but I don't know if it's "valuable". I think
> you're going about it the wrong way, though. You don't need all those
> named
> ranges; just a couple. I'm not quite sure EXACTLY what you're looking for,
> but I created a workbook I hope explains what I'm suggesting. You can
> download it at http://www.myexpertsonline.com/freedls/pricelist.xls. There
> are no macros in it.
>
> Note the named ranges I used, and the ranges they cover. Note the data
> validation in E10, and the vlookups in E12 and E14. Note that you can hide
> the sheet called PriceList.
>
> I hope it helps!
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "InfinityDesigns" <InfinityDesigns@discussions.microsoft.com> wrote in
> message news:E64AA12E-DE65-418D-9549-13819DFC5BCF@microsoft.com...
>> I am not sure what happened to my last thread. I was getting valuable
>> information from someone but my thread disappeared. What I am trying to
> do
>> is this, If I type =HollywoodHills in K10 it returns a value from that
>> defined name of $34.56 which is the retail price of that product. I have
>> another defined name of "HollywoodHillsC" which will return the value of
>> $20.25 which is the wholesale price of the same product. The defined
> names
>> will change with each product line on the form. So I want to be able to
> type
>> =HollywoodHills in K10 have another cell say N10 that will not be visible
> to
>> the user that will have the same name in it, and then use a formula or
> macro
>> to add the letter "C" to the end of it to return the wholesale price of
>> whatever product is in K10. This other cell will not be visible by the
> user
>> because some users should not have access to wholesale pricing. Can
> anyone
>> help???
>
>

[InfinityDesigns]

Hi Anne,
I looked at the workbook that you had a link to. I understand how to make a
workbook with the retail and wholesale prices in it. What I am trying to do
is access those prices through defined names in a totally separate workbook.
I have the defined names already established, and I can access them from the
workbook in question, the problem is that I am wanting to be able to access
the wholesale price of each individual product without the user having to
enter the defined name manually, in order to track job expenses. I hope I am
making myself clear. Thank you for your willingness to help.

"Anne Troy" wrote:

> Hi. I tried to help before, but I don't know if it's "valuable". I think
> you're going about it the wrong way, though. You don't need all those named
> ranges; just a couple. I'm not quite sure EXACTLY what you're looking for,
> but I created a workbook I hope explains what I'm suggesting. You can
> download it at http://www.myexpertsonline.com/freedls/pricelist.xls. There
> are no macros in it.
>
> Note the named ranges I used, and the ranges they cover. Note the data
> validation in E10, and the vlookups in E12 and E14. Note that you can hide
> the sheet called PriceList.
>
> I hope it helps!
> *******************
> ~Anne Troy
>
> www.OfficeArticles.com
> www.MyExpertsOnline.com
>
>
> "InfinityDesigns" <InfinityDesigns@discussions.microsoft.com> wrote in
> message news:E64AA12E-DE65-418D-9549-13819DFC5BCF@microsoft.com...
> > I am not sure what happened to my last thread. I was getting valuable
> > information from someone but my thread disappeared. What I am trying to
> do
> > is this, If I type =HollywoodHills in K10 it returns a value from that
> > defined name of $34.56 which is the retail price of that product. I have
> > another defined name of "HollywoodHillsC" which will return the value of
> > $20.25 which is the wholesale price of the same product. The defined
> names
> > will change with each product line on the form. So I want to be able to
> type
> > =HollywoodHills in K10 have another cell say N10 that will not be visible
> to
> > the user that will have the same name in it, and then use a formula or
> macro
> > to add the letter "C" to the end of it to return the wholesale price of
> > whatever product is in K10. This other cell will not be visible by the
> user
> > because some users should not have access to wholesale pricing. Can
> anyone
> > help???
>
>
>

[Anne Troy]

Hi, ID. You're likely to get more in-depth help at www.vbaexpress.com. You
can even zip and upload your file there. I think you'll get further faster.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"InfinityDesigns" <InfinityDesigns@discussions.microsoft.com> wrote in
message news:D413A0CF-ECF4-45C2-88F5-1842B93931C1@microsoft.com...
> Hi Anne,
> I looked at the workbook that you had a link to. I understand how to make
a
> workbook with the retail and wholesale prices in it. What I am trying to
do
> is access those prices through defined names in a totally separate
workbook.
> I have the defined names already established, and I can access them from
the
> workbook in question, the problem is that I am wanting to be able to
access
> the wholesale price of each individual product without the user having to
> enter the defined name manually, in order to track job expenses. I hope I
am
> making myself clear. Thank you for your willingness to help.
>
> "Anne Troy" wrote:
>
> > Hi. I tried to help before, but I don't know if it's "valuable". I think
> > you're going about it the wrong way, though. You don't need all those
named
> > ranges; just a couple. I'm not quite sure EXACTLY what you're looking
for,
> > but I created a workbook I hope explains what I'm suggesting. You can
> > download it at http://www.myexpertsonline.com/freedls/pricelist.xls.
There
> > are no macros in it.
> >
> > Note the named ranges I used, and the ranges they cover. Note the data
> > validation in E10, and the vlookups in E12 and E14. Note that you can
hide
> > the sheet called PriceList.
> >
> > I hope it helps!
> > *******************
> > ~Anne Troy
> >
> > www.OfficeArticles.com
> > www.MyExpertsOnline.com
> >
> >
> > "InfinityDesigns" <InfinityDesigns@discussions.microsoft.com> wrote in
> > message news:E64AA12E-DE65-418D-9549-13819DFC5BCF@microsoft.com...
> > > I am not sure what happened to my last thread. I was getting valuable
> > > information from someone but my thread disappeared. What I am trying
to
> > do
> > > is this, If I type =HollywoodHills in K10 it returns a value from that
> > > defined name of $34.56 which is the retail price of that product. I
have
> > > another defined name of "HollywoodHillsC" which will return the value
of
> > > $20.25 which is the wholesale price of the same product. The defined
> > names
> > > will change with each product line on the form. So I want to be able
to
> > type
> > > =HollywoodHills in K10 have another cell say N10 that will not be
visible
> > to
> > > the user that will have the same name in it, and then use a formula or
> > macro
> > > to add the letter "C" to the end of it to return the wholesale price
of
> > > whatever product is in K10. This other cell will not be visible by
the
> > user
> > > because some users should not have access to wholesale pricing. Can
> > anyone
> > > help???
> >
> >
> >

[Dave Peterson]

You had two responses that suggested that you use a UserDefinedFunction to
return the name from the formula at one of your other posts.

Didn't either of them work?

InfinityDesigns wrote:
>
> I am not sure what happened to my last thread. I was getting valuable
> information from someone but my thread disappeared. What I am trying to do
> is this, If I type =HollywoodHills in K10 it returns a value from that
> defined name of $34.56 which is the retail price of that product. I have
> another defined name of "HollywoodHillsC" which will return the value of
> $20.25 which is the wholesale price of the same product. The defined names
> will change with each product line on the form. So I want to be able to type
> =HollywoodHills in K10 have another cell say N10 that will not be visible to
> the user that will have the same name in it, and then use a formula or macro
> to add the letter "C" to the end of it to return the wholesale price of
> whatever product is in K10. This other cell will not be visible by the user
> because some users should not have access to wholesale pricing. Can anyone
> help???

--

Dave Peterson

[InfinityDesigns]

To be honest with you, I don't know how to use a user defined function. I
would like to learn how, I am sure it would be helpful.

"Dave Peterson" wrote:

> You had two responses that suggested that you use a UserDefinedFunction to
> return the name from the formula at one of your other posts.
>
> Didn't either of them work?
>
> InfinityDesigns wrote:
> >
> > I am not sure what happened to my last thread. I was getting valuable
> > information from someone but my thread disappeared. What I am trying to do
> > is this, If I type =HollywoodHills in K10 it returns a value from that
> > defined name of $34.56 which is the retail price of that product. I have
> > another defined name of "HollywoodHillsC" which will return the value of
> > $20.25 which is the wholesale price of the same product. The defined names
> > will change with each product line on the form. So I want to be able to type
> > =HollywoodHills in K10 have another cell say N10 that will not be visible to
> > the user that will have the same name in it, and then use a formula or macro
> > to add the letter "C" to the end of it to return the wholesale price of
> > whatever product is in K10. This other cell will not be visible by the user
> > because some users should not have access to wholesale pricing. Can anyone
> > help???
>
> --
>
> Dave Peterson
>

[Norman Jones]

Hi InfinityDesigns,

"InfinityDesigns" <InfinityDesigns@discussions.microsoft.com> wrote in
message news:BA4CFBD8-7F4E-495F-BE38-550FF4776C4D@microsoft.com...
> To be honest with you, I don't know how to use a user defined function. I
> would like to learn how, I am sure it would be helpful.

As a starting point visit David McRitchie's 'Getting Started with Macros and
User Defined Functions' page at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


---
Regards,
Norman

[Dave Peterson]

You could try reading the instructions in one of the earlier posts:

http://groups.google.co.uk/group/mic...37182e45cf7c9c

or
http://tinyurl.com/7unae



InfinityDesigns wrote:
>
> To be honest with you, I don't know how to use a user defined function. I
> would like to learn how, I am sure it would be helpful.
>
> "Dave Peterson" wrote:
>
> > You had two responses that suggested that you use a UserDefinedFunction to
> > return the name from the formula at one of your other posts.
> >
> > Didn't either of them work?
> >
> > InfinityDesigns wrote:
> > >
> > > I am not sure what happened to my last thread. I was getting valuable
> > > information from someone but my thread disappeared. What I am trying to do
> > > is this, If I type =HollywoodHills in K10 it returns a value from that
> > > defined name of $34.56 which is the retail price of that product. I have
> > > another defined name of "HollywoodHillsC" which will return the value of
> > > $20.25 which is the wholesale price of the same product. The defined names
> > > will change with each product line on the form. So I want to be able to type
> > > =HollywoodHills in K10 have another cell say N10 that will not be visible to
> > > the user that will have the same name in it, and then use a formula or macro
> > > to add the letter "C" to the end of it to return the wholesale price of
> > > whatever product is in K10. This other cell will not be visible by the user
> > > because some users should not have access to wholesale pricing. Can anyone
> > > help???
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[InfinityDesigns]

Hi Dave,
The light is begining to go on. I appreciate your patience with me, as I am
fairly new to all of this. Thank you for sending the link to the info from
the other thread, for some reason I could no longer see it on line. I went
to Insert Procedure, clicked on Function and made it public, then pasted your
UDF in that module. I then put =INDIRECT(MID(getformula(A2),2Â*,255)&"c") and
altered it slightly. I am assuming the "A2" is the cell that I am wanting
the formula to go, I am also assuming the "2 & 255" are columns and rows that
I want to retrieve the formula from, Is that correct? If it is, I altered
those references to what I am looking for and tried it. It gave me an error
message that read "ambiguous name GetFormula". What does that mean? Thank
you again

"Dave Peterson" wrote:

> You could try reading the instructions in one of the earlier posts:
>
> http://groups.google.co.uk/group/mic...37182e45cf7c9c
>
> or
> http://tinyurl.com/7unae
>
>
>
> InfinityDesigns wrote:
> >
> > To be honest with you, I don't know how to use a user defined function. I
> > would like to learn how, I am sure it would be helpful.
> >
> > "Dave Peterson" wrote:
> >
> > > You had two responses that suggested that you use a UserDefinedFunction to
> > > return the name from the formula at one of your other posts.
> > >
> > > Didn't either of them work?
> > >
> > > InfinityDesigns wrote:
> > > >
> > > > I am not sure what happened to my last thread. I was getting valuable
> > > > information from someone but my thread disappeared. What I am trying to do
> > > > is this, If I type =HollywoodHills in K10 it returns a value from that
> > > > defined name of $34.56 which is the retail price of that product. I have
> > > > another defined name of "HollywoodHillsC" which will return the value of
> > > > $20.25 which is the wholesale price of the same product. The defined names
> > > > will change with each product line on the form. So I want to be able to type
> > > > =HollywoodHills in K10 have another cell say N10 that will not be visible to
> > > > the user that will have the same name in it, and then use a formula or macro
> > > > to add the letter "C" to the end of it to return the wholesale price of
> > > > whatever product is in K10. This other cell will not be visible by the user
> > > > because some users should not have access to wholesale pricing. Can anyone
> > > > help???
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[InfinityDesigns]

Hi Dave,

I figured out what the ambiguous name was, and I am no longer getting an
error message. Now the formula I typed into the cell is returning #VALUE!.
The only thing I can think of is I am not decifering what the formula means
correctly. What can you suggest? Thank you!

Scott

"Dave Peterson" wrote:

> You could try reading the instructions in one of the earlier posts:
>
> http://groups.google.co.uk/group/mic...37182e45cf7c9c
>
> or
> http://tinyurl.com/7unae
>
>
>
> InfinityDesigns wrote:
> >
> > To be honest with you, I don't know how to use a user defined function. I
> > would like to learn how, I am sure it would be helpful.
> >
> > "Dave Peterson" wrote:
> >
> > > You had two responses that suggested that you use a UserDefinedFunction to
> > > return the name from the formula at one of your other posts.
> > >
> > > Didn't either of them work?
> > >
> > > InfinityDesigns wrote:
> > > >
> > > > I am not sure what happened to my last thread. I was getting valuable
> > > > information from someone but my thread disappeared. What I am trying to do
> > > > is this, If I type =HollywoodHills in K10 it returns a value from that
> > > > defined name of $34.56 which is the retail price of that product. I have
> > > > another defined name of "HollywoodHillsC" which will return the value of
> > > > $20.25 which is the wholesale price of the same product. The defined names
> > > > will change with each product line on the form. So I want to be able to type
> > > > =HollywoodHills in K10 have another cell say N10 that will not be visible to
> > > > the user that will have the same name in it, and then use a formula or macro
> > > > to add the letter "C" to the end of it to return the wholesale price of
> > > > whatever product is in K10. This other cell will not be visible by the user
> > > > because some users should not have access to wholesale pricing. Can anyone
> > > > help???
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Your post had some funny characters in it. I'm not sure if those funny
characters reside in your code--or just in the post.

This is the UDF that I use (no change from previous version):

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

The formula that I suggested in the worksheet cell was:
=INDIRECT(MID(getformula(A2),2,255)&"c")

The mid() function retrieves stuff from the string. In this case, the string is
what the getformula() returns.

The 2 means to start with the 2nd character. The 255 means to extend it 255
characters--255 is bigger than any string/name you'll be using. (In essence, it
just removes the leading equal sign from your formula.)



InfinityDesigns wrote:
>
> Hi Dave,
>
> I figured out what the ambiguous name was, and I am no longer getting an
> error message. Now the formula I typed into the cell is returning #VALUE!.
> The only thing I can think of is I am not decifering what the formula means
> correctly. What can you suggest? Thank you!
>
> Scott
>
> "Dave Peterson" wrote:
>
> > You could try reading the instructions in one of the earlier posts:
> >
> > http://groups.google.co.uk/group/mic...37182e45cf7c9c
> >
> > or
> > http://tinyurl.com/7unae
> >
> >
> >
> > InfinityDesigns wrote:
> > >
> > > To be honest with you, I don't know how to use a user defined function. I
> > > would like to learn how, I am sure it would be helpful.
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > You had two responses that suggested that you use a UserDefinedFunction to
> > > > return the name from the formula at one of your other posts.
> > > >
> > > > Didn't either of them work?
> > > >
> > > > InfinityDesigns wrote:
> > > > >
> > > > > I am not sure what happened to my last thread. I was getting valuable
> > > > > information from someone but my thread disappeared. What I am trying to do
> > > > > is this, If I type =HollywoodHills in K10 it returns a value from that
> > > > > defined name of $34.56 which is the retail price of that product. I have
> > > > > another defined name of "HollywoodHillsC" which will return the value of
> > > > > $20.25 which is the wholesale price of the same product. The defined names
> > > > > will change with each product line on the form. So I want to be able to type
> > > > > =HollywoodHills in K10 have another cell say N10 that will not be visible to
> > > > > the user that will have the same name in it, and then use a formula or macro
> > > > > to add the letter "C" to the end of it to return the wholesale price of
> > > > > whatever product is in K10. This other cell will not be visible by the user
> > > > > because some users should not have access to wholesale pricing. Can anyone
> > > > > help???
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson