I used this formula:

=pull("'"&modeldir&"\[jointdefaults.xls]Boltrows'!$E$5")

And Harlan's UDF worked fine for me.

(I still wanted you to keep the =pull() portion.)

Norm wrote:

Dave,

Still having trouble.

ModelDir = C:\TeklastructuresModels\11.2
Cell which returns the correct result =
"='C:\TeklaStructuresModels\11.2\[jointdefaults.xls]Boltrows'!$E$5"

Cell which returns
"='C:\TeklaStructuresModels\11.2\[jointdefaults.xls]Boltrows'!$E$5" is =
"="='"&ModelDir&"\[jointdefaults.xls]Boltrows'!$E$5""

I have tried variations of this as well

Cell =
"="'"&ModelDir&"\[jointdefaults.xls]Boltrows'!$E$5"" returns
"'C:\TeklaStructuresModels\11.2\[jointdefaults.xls]Boltrows'!$E$5"

"Norm" wrote:

Dave,

Thanks! That did work, however I need to be able use the value ModelDir for
the path and folder location of the xls file. Is that possible. Your method
leaves me with a static result.

Norm

"Dave Peterson" wrote:

I'd copy that formula to an empty cell.
Then I'd remove the pull( and last ).
Then with the cursor still in the formula bar, hit F9 to convert it to text.
Then add the = sign to the formula.

Did that work ok?

If not, maybe your modeldir isn't correct????

I often open the other workbook, build a formula that points to that cell, then
close that workbook so I can see how excel builds that long string.

You may want to post back both versions -- a direct link and the formula you're
using (and what's in modeldir) if you have trouble.



Norm wrote:

Can someone help me out and explain (show an example formula) of how to use
Harlan's pull function?

It sounds exactly like what I need, but I cannot get it to work. Below is
what I have input into the cell which I need to recover data from a closed
workbook.

=pull("'"&ModelDir&"\[jointdefaults.xls]Defaults'!$C$21")

am I even close?

The spreadsheet is always named jointdefaults.xls and the folder path is
obtained in the range ModelDir.


--

Dave Peterson


--

Dave Peterson