Results 1 to 6 of 6

Thread: Tab Sum (2000)

  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tab Sum (2000)

    I am checking to see if anyone might know a more efficient way to do this (see attached). Data can be entered on Jan, Feb, or Mar tabs and the order of the employee will change (meaning Andy who is listed first on Jan will not always be the first employee listed each month).

    In the YTD I have used vlookup functions to sum each month together based on the employee name selected from the dropdown on the YTD tab. The user can select names in any order and as many names or as little names may be selected as desired.

    Hopefully the workbook demonstates well enough what I am doing. I am just looking for a more efficient way to do it if any knows how. The reason being is that this sample is just for three months and three data categories. My real project is 12 months, 22 data categories, and 20 people...

    Thanks!!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Tab Sum (2000)

    I am not a proponent of creatiing multiple identically formatted sheets. Why not combine them and add a column to indicate the month?

    A pivot table will give you the results directly...

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tab Sum (2000)

    I agree with Steve's recommendation. If, however, you prefer to keep the current setup, you can use the 3D functions from here.

    See attached workbook.

  4. #4
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Sum (2000)

    I completely agree about not having multiple identical tabs. However, the YTD tab (or pivot table) is not the only desired result. Each month has to be printed, studied, and used as a tool for store improvements. There is a lot of data for each month, and it must all still be organized in a readable fashion. However, you might be on to something with the pivot table. I might be able to create a sheet that will be hidden to use as a data source for a pivot table.

  5. #5
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Sum (2000)

    Thanks Hans! I have never used 3D functions before. This does exactly what I was looking to do and the formulas are easy to understand. I am limited on this project and cannot use macros, but I will definitely use this in the future.

    I took Steve's idea of the pivot table, and worked with it a little bit. I added a sheet (which will be hidden) that just contains all of the data from each tab, I can then do a simple sumif() on the YTD tab to look for appropriate values.

    Thanks for the help and the ideas!!

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Tab Sum (2000)

    That can be done by using autofiliter or even a pivot table "page item" (put the month in the page)

    If you create and autofilter on the list, you can filter on "Jan" and get the display to essentially look like the sheet called "Jan". You can even use the subtotal function to get stats (average, min, max, etc) for teh displayed values.

    This has the advantage also of being able to filter on a name and get what would be printouts of each individual (like each having their own sheet) without having to create it.

    Steve

Posting Permissions

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