Pull function is not working, gives "Value!" error...
Thanks..

"Dave Peterson" wrote:

If you put the workbook name into another cell, you could use =indirect() to
build the reference to the other workbook.

The bad news is that =indirect() will return an error if that other workbook
isn't open.

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

============
Or if this was a one time shot, you could build your formulas as strings and do
a little work:

="$$$$$='F:\CONFERENCE\[" & a2 & "]Sheet1'!$B$1"
or if you have to preserve leading 0's:
="$$$$$='F:\CONFERENCE\[" & text(a2,"00000") & "]Sheet1'!$B$1"

And drag down.

Now you have a bunch of formulas that evaluate to strings that look kind of like
formulas.

Select that column
edit|copy
followed by
edit|paste special|values

Now you've strings that look like formulas.

Save your workbook here!

With that column still selected:
edit|replace
what: $$$$$=
with: =
replace all

And your strings are formulas.

Be very careful. If you made a mistake in the path, you'll be dismissing
prompts asking you for the new file location. (I'd test on just a couple of
cells first.)

But if you have to kill excel to get out of all 1200 prompts, you saved right
before that last step!

Nimit Mehta wrote:

A folder contains 1200 worksheets named based on Ac/ nos. of my clients.
Another main sheet linking them looks like this.

AC NO NAME CODE BALANCE
31648 A B RAMI 18424100 188.85
31734 A K DESAI 249 61.50
31777 A M SHAH 13527 230.15

Numbers in coloumn A are manual entries.
Name in B has this reference as of now. (
='F:\CONFERENCE\[31648.xls]Sheet1'!$A$1 )

Code in C has this reference. ( ='F:\CONFERENCE\[31648.xls]Sheet1'!$B$1 )

And so on..

I tried this : ="'F:\CONFERENCE\["&A1&".xls]Sheet1'!" which gives me
'F:\CONFERENCE\[31648.xls]Sheet1'! in the cell, when i enter 31648 in A1. But
corresponding value is not displayed.
Since there are 1200 files its very difficult to manually type references
for all of them.
TIA.

--

Dave Peterson