Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    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,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  3. #3
    3 Star Lounger
    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.
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>

  5. #5
    3 Star Lounger
    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.
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  6. #6
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    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

  7. #7
    3 Star Lounger
    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 step-by-step instructions.

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

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •