+ Reply to Thread
Results 1 to 2 of 2

using indirect across workbooks

  1. #1
    Registered User
    Join Date
    07-08-2011
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2011
    Posts
    7

    Talking using indirect across workbooks

    My original Problem was creating a 3d lookup with multiple criteria. I did this originally all in one workbook where the sheet labeled "Summary" contained the formulas to complete my lookup.

    The goal of "Summary" was to be able to control the entire sheet by entering the month (abbreviated) that I want data for into cell A2.

    To do this I first created the formula below and entered it into Cells B4:X4. This formula looks at Summary A2 and matches the value (month) with the sheet (Jan:July which is written grouped as "'"&list&"'" in Summary A8:A14) that holds the same value (month) in cell A2. When the correct sheet is found it takes the values from B4:X4 and enters them into Summary B4:X4.

    =IF(SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!$A$2"),$A$2,INDIRECT("'"&list&"'!b$4")))=0,"",(SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!$A$2"),$A$2,INDIRECT("'"&list&"'!b$4")))))

    That process was necessary because the dates are not straight through, they are only business days which change every month.

    After that I created the formula:

    =SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!$B$4:$X$4"),B$4,INDIRECT("'"&list&"'!B$14:X$14")))

    this looks through all the sheets until it finds the date that matches (purpose of previous formula) and then enters the value of the appropriate sheet cell B14

    I have the workbook working perfectly. All that needs to be done is change the month in worksheet 'Summary' cell A2 and the worksheet, finds the correct dates, and the correct values.

    I was very excited to get this correct but now I have a new problem.

    The sheet labeled Summary CAN NOT be part of the workbook BrianFormulas.xlsx, it needs to its own workbook. Also creating a worksheet within BrianFormulas that holds all the dates is out of the question.

    So basically I really need some help here. I need to essentially use the same formulas but have them looking through the workbook BrianFormulas.xlsx from the workbook Brian Summary.xlsx

    I need to finish this project by tomorrow so any help would be greatly appreciated
    Attached Files Attached Files
    Last edited by robgardner15; 07-08-2011 at 06:00 AM. Reason: requested by admin

  2. #2
    Registered User
    Join Date
    07-08-2011
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2011
    Posts
    7

    Re: NEED HELP using indirect across workbooks

    Come on guys I could really use some help! Please let me know if the issue is not clear

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1