References and links.

[Nimit Mehta]

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]

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!

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.

 

[Nimit Mehta]

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

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!

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]

I'd try it again.

Harlan's code works ok for me.

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

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!

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.

 

[Nimit Mehta]

I searched google for help on harlan's pull function, could not find any.
Lastly i found this.

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

with posters name "Dave Peterson" under it.
I still could not get this to work. I would be very thankful if you can type
the formula that i would be using for my worksheet. Also can you explain me
options of the same function? A workbook in D:\accounts called "65231.xls"
with sheet "Sheet1" exists. I need to pull cell H5. I made main sheet like
this..

A B C D
65231 D:\accounts\ [65231.xls] Sheet1!H5

and tried using Pull function like this.

=pull("'"&D:\accounts&"\["A2".xls]Sheet1!H5")
Not working..
I only have coloumn A as of now with 1200 ac/nos. ( Dynamic and keep
changing ) Rest entries in coloumn B,C and D are static constants for all
workbooks.
Thanks.

I'd try it again.

Harlan's code works ok for me.

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

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.

 

[Harlan Grove]

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

....

Note that the formula above has single quotes/apostrophes immediately
preceding the drive letter *AND* the exclamation point after the worksheet
name, so the full pathname plus worksheet name is delimited by beginning and
ending single quotes/apostrophes.

A B C D
65231 D:\accounts\ [65231.xls] Sheet1!H5

and tried using Pull function like this.

=pull("'"&D:\accounts&"\["A2".xls]Sheet1!H5")
Not working..

....

Note that this has a single quote/apostrophe immediately preceding the drive
letter *BUT* *NOT* the exclamation point. Change your formula to

=pull("'"&D:\accounts&"\["A2".xls]Sheet1'!H5")

Whenever debugging errors from pull calls, delete pull from the formula and
evaluate the remainder of the formula. In your formula above, the formula
would have become

=("'"&D:\accounts&"\["A2".xls]Sheet1!H5")

and it would have evaluated to

'D:\accounts\[65231.xls]Sheet1!H5

Then insert an equal sign at the beginning of this,

='D:\accounts\[65231.xls]Sheet1!H5

and press [Enter]. Excel would have given you a syntax error, which would
have shown that there was a problem in the constructed textref.