External Links built from local cell contents

[fred3]

I've read a little about this but haven't found a satisfying answer.
I have a summary / report file that takes information from other structurally-identical .xlsm files and summarizes it.

So, a row might be:
A1 has filename
B1 has [filename.xlsm]Sheet1!A1
C1 has [filename.xlsm]Sheet1!F1
etc.
And, of course, there are many rows each with a different filename.

What I need to do is to use the contents of column A in constructing the formulas in columns B, C, etc.

How?

 

[Deepak]

Hope!! you are in need of something like this...

=INDIRECT("["&A1&"]Sheet1!A")

 

[Somendra Misra]

Hi fred,

As @Deepak suggested you can use INDIRECT function to refer to other workbook. But the problem with INDIRECT function is that it cannot be used on closed workbook, so you will get results only & only if all the attached workbooks are opened. VBA can help you to do the task. Visit below link for the same

http://numbermonger.com/2012/02/11/excel-pull-function-creating-dynamic-links-to-closed-workbooks/

From the comment section you will get the link to download the latest version:

numbermonger.com/2012/02/11/excel-pull-function-creating-dynamic-links-to-closed-workbooks/

Use the PULL function given on the links.

Regards

 

[fred3]

dEEPAK,
Yes, I'd figured out the INDIRECT approach and ran into the open file / closed file issue with that. Seems it's not the first time I'd hit that wall. It would be great to remember these things!

Somendra,
Thanks for that reference to PULL.
It took a bit of doing to get it working as the copy of "'" came out something different like '''''. Seems like I just read a warning about this sort of thing here. i.e. manually type these things.

I was a little put off by the terse nature of the PULL example and apparent lack of documentation but I worked it all out and learned a lot about what it will do.
As a step forward I created a number of columns:
Path OpenBracket File CloseBracket Sheet Cell
C:\word\data [ ClosedBook.xlsx ] Sheet1'! $A$1

Then used PULL("'"&Path&OpenBracket&File&CloseBracket&Sheet&Cell)

Then, figuring that shorter is better, moved the brackets into their adjacent fields.
Anyway, PULL doesn't seem to mind.
The only odd thing is the beginning "'" while having the closing ' in the Sheet field.
Then I realized that this is only an example and this worked just as well:

Path File Sheet Cell
'C:\word\data[ ClosedBook.xlsx ]Sheet1'! $A$1
PULL(Path&File&Sheet&Cell)

So, there seems to be lots of flexibility once you figure out you're dealing with strings throughout.
Breaking things up this way makes it a bit easier to structure the component cells.
(For example, the File field in this case was =C$2&".xlsx where C2 has filename with no extension in it.

Thank you!!

 

[fred3]

Well .. I guess I should say "so far, so good".
I have a summary worksheet in a separate file which needs to Pull data.
There are about 200 rows (one per file that is pulled from) and about 25 pulled data cells per row.

Using Pull, the objective was to make the summary cells generic so I could simply copy them to a new row and have them pull from a new filename.
So, I edited the first row to reflect the new formulas.
Then I copied and pasted this row into the others.

At first the app hung and I had to repeat the edit.
So, next I turned off automatic calculation and pasted into one row at a time.
It seemed to take a long time but it worked.
Then I pasted 5 rows and it really took a long time but it also worked.
Then I pasted all the remaining rows and it's still running after hours with about 30% cpu utilization.
I suspect it will never finish / may be in a loop.
In the mean time the program is pretty much unresponsive to mouse clicks, etc. and I see no progress in the sheet (when I pasted the new row, it brought all of the numeric values with it so one can tell if the update has happened because the numbers change to unique values).

 

[fred3]

As above, PULL appears to be slow for others as well. In researching it, I found this reference on Experts Exchange:
http://www.experts-exchange.com/Sof...Office_Suites/MS_Office/Excel/Q_27729755.html
and I got a little help with it there:
http://www.experts-exchange.com/Sof...Office_Suites/MS_Office/Excel/Q_28466130.html
It seems that byundt's approach is faster than PULL by quite a bit but I'm not so sure why.

It looks like both of them construct external link cell formulas.

I wonder if the links are updated in real time? Could that be the difference?