Results 1 to 7 of 7

20060822, 17:32 #1
 Join Date
 May 2002
 Location
 Mpls, Minnesota, USA
 Posts
 271
 Thanks
 0
 Thanked 0 Times in 0 Posts
formula to get data from Open Workbooks (Excel 2003 SP2)
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,
ChuckChuck Reimer
I'm from the Government and I'm here to help...

20060822, 18:55 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: formula to get data from Open Workbooks (Excel 2003 SP2)
For example, in cell D16, you could use this formula:
<code>
=INDIRECT("'U:PROJECTSWallyBachtleFromWally["&Quarter1and2Book&"]"&$B16&"'!$D$35")
</code>
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.

20060822, 18:59 #3
 Join Date
 May 2002
 Location
 Mpls, Minnesota, USA
 Posts
 271
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: formula to get data from Open Workbooks (Excel 2003 SP2)
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.
ChuckChuck Reimer
I'm from the Government and I'm here to help...

20060822, 19:03 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: formula to get data from Open Workbooks (Excel 2003 SP2)
Actually, you don't. And INDIRECT will not work with closed workbooks, so you should be able to use
<code>
=INDIRECT("'["&Quarter1and2Book&"]"&$B16&"'!$D$35")</code>

20060822, 19:09 #5
 Join Date
 May 2002
 Location
 Mpls, Minnesota, USA
 Posts
 271
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: formula to get data from Open Workbooks (Excel 2003 SP2)
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.
ChuckChuck Reimer
I'm from the Government and I'm here to help...

20060822, 19:34 #6
 Join Date
 Nov 2002
 Location
 New York, New York, USA
 Posts
 273
 Thanks
 0
 Thanked 19 Times in 19 Posts
Re: formula to get data from Open Workbooks (Excel 2003 SP2)
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

20060823, 11:23 #7
 Join Date
 May 2002
 Location
 Mpls, Minnesota, USA
 Posts
 271
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: formula to get data from Open Workbooks (Excel 2003 SP2)
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 stepbystep instructions.
It is really nice to know there is a place to turn when in need of help.
ChuckChuck Reimer
I'm from the Government and I'm here to help...