Summarise data from different cells of multiple worksheets

{ 12 Comments }

By Ashish Mathur · · · Leave a comment

In each of the four worksheets (one for each week) of this workbook, there is a two column database showing Employee ID and Revenue earned by that employee in that week.  Not all employees appear on all sheets and there is no fixed cell reference for any employee on any sheet – therefore employee A001 can be on cell B4 of week 1 sheet but on cell B10 on week 2 sheet.

In the Summary, all unique Employee ID’s have been listed in range B4:B17.  The question is “How can the employee wise monthly revenue be computed in range C4:C17”.

I can think of two ways of solving this problem.

Method 1

Create a Pivot Table from four worksheets (without first copying data from all four worksheets into one).  To create a Pivot Table from multiple worksheets, you may refer to my solution at this link.

Method 2

Write a multi sheet array formula.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • I have a spreadsheet with a list of Employees sometimes these employee are listed more that once. I would like to perform a lookup on another worksheet to add up their FTE and put it in one cell. A1 Employee B1 .50, A2 Same Employee B2 .25, A3 Same Employee B3 .25, for a total of 1.0 Full Time Equ. Do you know what formula I would use to lookup the name of the employee and return the total of the FTE to one cell on a different spreadsheet.

    Reply

    • Hi,

      Could you kindly be more descriptive. Specifically:

      1. Please layout the exact structure on Table1 – clearly show the cells where you want the results to appear
      2. List down all unique ID’s in column A of Table1

      Reply

  • I want to create a program for a school club where if a student signs in for one of the club events, a master spreadsheet will take into account of that and it counts how many events a student went.

    Here is a sample of what I want to do (third column of master worksheet would show how many times a person has “signed in”, which would basically be writing their names in the other worksheets):

    https://skydrive.live.com/redir?resid=5D43E9A1356C306!107&authkey=!AGw6El60NCijuCI

    Reply

      • Hello,

        Thanks for the solution, it really was helpful. I actually would like to ask a few more things. Would it be possible that whenever I write in a new name in the other worksheets, that name would appear in the summary worksheet below all other preexisting names with their respective number of events? In the link below, for the Week 1 tab, I wrote in “Person Eight” and want that name to appear in the Summary tab, below “Person Seven” for instance.

        https://skydrive.live.com/redir?resid=5D43E9A1356C306!112&authkey=!AAj4CrHYEHYeB5k

        Thanks again and I really appreciate the help.

        Reply

        • Hi,

          That would be difficult to accomplish. You essentially want to summarise data from multiple worksheets and have the Summary worksheet update for new rows of data added to the individual worksheets.

          I think you should instead focus on “Creating a Pivot Table from multiple worksheets” of a workbook. Refer to my solution at the following link.

          Hope this helps.

          Reply

  • In each of the four worksheets (one for each week) of this workbook, there is a two column database showing Employee ID and Revenue earned by that employee in that week. Not all employees appear on all sheets and there is no fixed cell reference for any employee on any sheet – therefore employee A001 can be on cell B4 of week 1 sheet but on cell B10 on week 2 sheet.

    In the Summary, all unique Employee ID’s have been listed in range B4:B17. The question is “How can the employee wise monthly revenue be computed in range C4:C17”.

    the above works helped me much
    “Workbook “

    Reply