Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jan 2002
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summarizing across tabs (Excell 2000)

    I need to enter payroll data for 3 employees. Their payroll is broken down by location and account #. I then summarize the information on a seperate tab. I need to summarize the info on each tab and then on the summary sheet, breaking the data down into account number. I have a few standard account numbers that we always use, but there are a hundred or so others that we use every once in a while. I need to make the sheet add up the totals for our standard accounts, but leave spaces for the non-standard accounts. Is it possible to have each have the summary look up the non-standard accounts across all three tabs and summarize the data for those accounts? This may be hard to grasp, so I've included a sample sheet. Thanks.
    Texasrocks.
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Summarizing across tabs (Excell 2000)

    texasrocks

    Check the attached workbook and see how I did it.

    BTW when you are testing it makes a lot of sense to change the numbers so that when you want to check the value returned by the formula(s) you can do that by simply looking at the values themselves.

    Now this is for the standard accounts, for the ad hoc ones I think a VLookup will do well.

    Build your worksheet with the simple stuff first and then we will see about the hard stuff.

    HTH

    Wassim
    Attached Files Attached Files
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Summarizing across tabs (Excell 2000)

    See attached. It's ugly but seems to do what you want.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Lounger
    Join Date
    Jan 2002
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summarizing across tabs (Excell 2000)

    Thanks for the suggestions. Now I can't seem to get the filter to work. It almost works, but it's pulling in one repeat value. I've attached real sheet this time. Let me know if you have any suggestions. Thanks again.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Summarizing across tabs (Excell 2000)

    Did you forget the attachment?

    I also had some trouble with the filter operation. I was pressed for time; I would change most of the hard-coded addresses to range names. The recurring account number -could be- due to the first account number being read as the header.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Lounger
    Join Date
    Jan 2002
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summarizing across tabs (Excell 2000)

    Yes, it looks like I forgot the attachment. I had to chop it up to fit it in, but this may give you the idea. Thanks again.
    Attached Files Attached Files

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Summarizing across tabs (Excell 2000)

    Try this one, you'll have to recreate the third employee's sheet and references, which should be easy with a little copy, find and replace. Also, there was no Prop data on the second employee, so I fabricated the data in bold red. Test it to see if it's stable.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Lounger
    Join Date
    Jan 2002
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summarizing across tabs (Excell 2000)

    Thank you once again!!! But once again I'm having trouble. I'm getting a :
    Method 'Range' of Object "_Global' Failed error. What causes that? Thanks. (Hopefully this'll be the last problem with this sheet...)

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Summarizing across tabs (Excell 2000)

    It's working for me; which line is erroring out for you, and did you make any changes?
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    Lounger
    Join Date
    Jan 2002
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summarizing across tabs (Excell 2000)

    IT WORKS!!!!!! You're my new hero. Thanks.

Posting Permissions

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