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