I am sorry I did not see your post showing the FTP site. Thank you and I
will look at it.
Scott

"Dave Peterson" wrote:

Through Harlan's Pull function.

That actually opens a second instance of excel and extracts the value that way.

InfinityDesigns wrote:

How then does the defined name access the values from cells in a closed
workbook?

"Dave Peterson" wrote:

But you'll still have the problem of the closed workbook.

Harlan Grove wrote a UDF called that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/

Look for pull.zip


InfinityDesigns wrote:

I am lost as to where to go from here. The only other thing I can think of
is copying the name from K10 and pasting it as a string in a hidden cell,
then accessing the string text from N10 and adding the "C" to it. Is there
any way of doing that?

"Dave Peterson" wrote:

=indirect() doesn't work with closed workbooks.

You may have to rethink your approach.

InfinityDesigns wrote:

Hi Dave,

Since my last posting, I have discovered what was causing the #REF!. By the
way your UDF and formula is ingenious. Now it seems as though I have a new
problem as a result, story of my life with this program. The reason I was
getting the #REF! was because the workbook that the formula got the original
defined name from was not open. I need to be able to reference these defined
names without requiring the source workbook being open. Is there any way of
doing that? My issues are getting narrowed down, and you have no idea how
much I appreciate your help. Thanx

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson