+ Reply to Thread
Results 1 to 5 of 5

Multiple Sheets Match & Index filtering by sheets

  1. #1
    Registered User
    Join Date
    11-24-2012
    Location
    Corona
    MS-Off Ver
    Excel 2007
    Posts
    13

    Multiple Sheets Match & Index filtering by sheets

    Hi there. So I'm brand new to this forum and am grateful for the assitance.

    I'm attaching a dummy spreadsheet that I need help summarizing. Basically there are (and will be more) sheets of data dated by week. As the Table is constantly changing based on the user's sorting I think I need some sort of Index and Match combination.
    My goal is to:
    1. sum columns of the agents based upon choosing one, several or all sheets
    2. Populate Agents based on an individual 'Office' or all offices.

    Thank you VERY much!
    Julian
    Attached Files Attached Files

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Multiple Sheets Match & Index filtering by sheets

    Hi Julian,

    Just my two cents, but this would be much easier in a contiguous block of data like the attached and then utilizing a pivot table.
    Attached Files Attached Files
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    11-24-2012
    Location
    Corona
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Multiple Sheets Match & Index filtering by sheets

    Thank you very much for the quick reply.

    The challenge with that is that there is actually over 100 agents weekly and each week also becomes important for the departments to analyze and sort...

    But your suggestion is very helpful for a different scenario. With that said, any thoughts on my convaluted problem? ;-)

    Thank you again.
    Julian
    Last edited by jeffreybrown; 11-24-2012 at 01:12 PM. Reason: As per Forum Rule #12, don't quote whole posts unless necessary-- it's just clutter.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Multiple Sheets Match & Index filtering by sheets

    Hi Julian,

    Your question is not all that convoluted, the challenge comes from understanding exactly what you want/need and if the model will sustain over time.

    To me, not sure over 100 agents would be a challenge and if the departments are analyzing and sorting, what better way to do this than through a pivot table. I would give the pivot table some serious thought, but by no means am I trying to talk to out of anything. Just food for thought.

    In your attachment, it would help to see an after example. My first impression is to use:

    Sum Based on a Single Criteria Across Multiple Sheets

    With that being said, I have to leave for awhile, but the other challenge I have faced with using this function, sheets names cannot be in the form of a date, but I have not tested this with your file. Maybe a mmm-dd will work, but just don't have time right now to test it.

  5. #5
    Registered User
    Join Date
    11-24-2012
    Location
    Corona
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Multiple Sheets Match & Index filtering by sheets

    Thank you Jeff. I have attached with an added sheet of two examples based on the criteria of
    1. Which worksheets chosen. I'm guessing the INDIRECT function would be used somehow
    2. Which office (or all) are chosen
    3. Table populates with relevant agents and then sums the columns

    Hope that makes a little more sense..

    Julian
    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