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

Eric wrote:

Thank everyone for suggestions
I get the file for pull.bas. Could you please give me any suggestions on how
to install this file for excel?
Thank everyone for suggtestions
Eric

"Dave Peterson" wrote:

Are you trying to use this function in a worksheet cell?

If you are, then this won't work. Functions in cells in worksheets can't insert
new sheets or change values in other cells. With very minor exceptions, these
functions can only return values to the cells that hold the function.

Harlan Grove wrote a UDF called PULL 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

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

-----------------
If I guessed wrong about what you're trying to do, then you may want to explain
it again. Addr is the address of the cell whose value should be returned.

A1 was just a cell that was used as a helper cell to obtain that value.

Eric wrote:

For the following function, does anyone have any suggestion on how to use
Addr instead of "A1" as an input? I try to replace "A1" with Addr,
but this does not work. Could anyone please give me any suggestions?
Thank in advance for any suggestions
Eric

Function TheValue(Path, WorkbookName, Sheet, Addr) As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Worksheets.Add
Range("A1").Formula = "='" & Path & "\[" & WorkbookName & "]" & Sheet &
"'!" & Addr
TheValue = Range("A1").Value
ActiveSheet.Delete
Application.ScreenUpdating = True
End Function

--

Dave Peterson


--

Dave Peterson