Closed Thread
Results 1 to 2 of 2

Sumproduct/sumif/indirect across multiple workbooks, worksheets, multiple criteria

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

    Wink Sumproduct/sumif/indirect across multiple workbooks, worksheets, multiple criteria

    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

    Also i'm not positive but pretty sure that Indirect does not work if the workbook it is looking at is closed. Please let me know if that is true and a way to resolve this issue.

    I need to finish this project by tomorrow so any help would be greatly appreciated
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,325

    Re: Sumproduct/sumif/indirect across multiple workbooks, worksheets, multiple criteri

    This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

    Thread Closed.

Closed 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