formula to get data from Open Workbooks (Excel 2003 SP2)

Topic

New Reply

I am trying to develop a formula that will get data contained in four known cells (D33, D34, D35 and D36) from two (open) Source workbooks.
Each source workbook has over 100 sheets (each the name of a city). Some of the tab names have a space in the name. (I cannot control that)
My summary workbook uses two named ranges (each containing the name of a workbook I am trying to access).
In column A of the summary workbook I have listed the city names as they appear on the tabs on the source workbook sheets.
I have been trying to build a formula that will grab the name of the workbook (from the range name) and access a cell address (D33, etc) from the sheet that is named in column A. I built a few formulas manually but would prefer a formula that can be updated by changing the names in the named ranges. I am attaching the summary workbook.
I am sure the problem is in my use of quotes when I try to build the formula.
I understand the 2 Source workbooks will have to be open for this to work (bring the data over).
For this task I prefer a formula, not a VBA solution.
Anyone got the time?

Thanks,
Chuck

Reply | Quote

Replies

For example, in cell D16, you could use this formula:

=INDIRECT("'U:PROJECTSWallyBachtleFromWally["&Quarter1and2Book&"]"&$B16&"'!$D$35")

The formula in E16 would be the same, except with $D$36 instead of $D$35.
The formulas in F16 and G16 would use Quarter3and4Book, and refer to $D$33 and $D$34.
You can fill these formulas down.

Reply | Quote

Hans,

Thanks. I will give that a try. I was thinking that if the workbook was open, I did not need the path.
I will try this first thing in the morning.

Thanks again.
Chuck

Reply | Quote

Actually, you don’t. And INDIRECT will not work with closed workbooks, so you should be able to use

=INDIRECT("'["&Quarter1and2Book&"]"&$B16&"'!$D$35")

Reply | Quote

Hans,

You make it look sooooo simple.
I messed with that formula for 90 minutes then spent another 45 minutes searching Woodys!
The closest I got was the formula turned in the text statement of the formula I was trying to get to calculate!

Thanks again.
Chuck

Reply | Quote

Chuck:

I have attached a proposed solution. See the Blue wording in the attached.

I recommend the VBA Macro for the last part of the solution but it can be done without VBA but it will take a L O N G time.

Good Luck

Tom Duthie

Hope this helps.

Tom Duthie

Reply | Quote

Tom/Hans,

Thanks!
Hans, both formulas worked great. (of course)
Tom, I will look at your method and see what I can learn form it. Thanks for the great step-by-step instructions.

It is really nice to know there is a place to turn when in need of help.

Chuck

Reply | Quote