+ Reply to Thread
Results 1 to 6 of 6

Sumproduct Multiple Worksheets, Columns and Rows

  1. #1
    Registered User
    Join Date
    02-24-2004
    Location
    Brisbane
    Posts
    29

    Need an expert - Sumproduct Multiple Worksheets, Columns and Rows

    Hi there

    I have a number of different worksheets ("Divisions") with data i would like to pull into a summary worksheet ("Summary").

    In the attached sample file, I would like to enhance the formula in cell F6 on the "Summary" worksheet, such that the formula will look for the data in the column in the selected "divisional" worksheets that correspond with the month in row 3 on the "Summary" worksheet

    Please note that the formula only gets data from the Divisions that are selected on the "Selection" worksheet. In this example, only DivisionsA and B are selected.

    My problem relates to the fact that there is no consistency in the columns used for the data on the Division worksheets. For example, "DivisionA" worksheet the 2006 Year numbers are in column F whereas "DivisionB" worksheet Year 2006 numbers are in column D etc. The row used for the "Year" criteria is consistent on all Division worksheets.

    Hope this makes sense.

    Thanks in advance for any assistance.

    Regards
    Peter
    Attached Files Attached Files
    Last edited by PeterW; 08-04-2007 at 11:16 PM. Reason: to add attachment

  2. #2
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Peter

    As you already have some code in your workbook, how about building a UDF to resolve this.

    Insert the function below into one of your general modules.

    Please Login or Register  to view this content.
    This would then be called as per normal in sheet summary
    F6: =mysumproduct(modWorksheetsSelected,$F$3,C6)


    HTH

    rylo

  3. #3
    Registered User
    Join Date
    02-24-2004
    Location
    Brisbane
    Posts
    29

    Works well

    Thanks Rylo ... wow ... u are obviously a wizard

  4. #4
    Registered User
    Join Date
    02-24-2004
    Location
    Brisbane
    Posts
    29

    Further enhancement

    Rylo ... your function works well .. however if there is more than one instance of the Account Names in column A, I need the function to include that value as well.

    By way of example, on the DivisionA worksheet, if there is another amount for rental income on row 10 (in addition to row7) then i need the formula to include this amount as well.

    Thanks in advance

  5. #5
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Peter

    how about

    Please Login or Register  to view this content.
    rylo

  6. #6
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269
    Here is a Formula version. Sample workbook below.
    Attached Files Attached Files

+ 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