Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Formula (97 SR-2 (k))

    My workbook has multiple work sheets - 5. Each work sheet has publisher info (1 column, but 7 publisher names) and whether the report is "Active" or "Issued" (another column). Some work sheets have 400+ reports listed. On the summary tab, I need to find a formula that tells me how many reports Jones has that are active, how many Smith has that are issued, etc. I know there are plenty of better ways to do, but this spreadsheet is a hand-me-down from someone who left the company and the bosses want to keep it. I tried a COUNTIF and SUMIF and a SUM with nested IF statements, but no luck. But I'm getting tired of sorting 5 work sheets 7 different ways. 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: Excel Formula (97 SR-2 (k))

    My suggestion combine them into 1 big sheet and then do a pivot table on all the data.

    Steve

  3. #3
    New Lounger
    Join Date
    Jul 2003
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula (97 SR-2 (k))

    That is a great idea, with one big but(t) in front of it - Upper management. They like the way the spreadsheet is set up and they don't want to change.

  4. #4
    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: Excel Formula (97 SR-2 (k))

    In those cases I would still do it, add datafilters and "other" bells and whistles and SHOW them a better way.

    The alternative is a fair amount of coding or some megaformulas, most likely arrays and such. We would need more details to write them.

    Steve

  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: Excel Formula (97 SR-2 (k))

    .. or as an alternate with Steve's approach, let upper management keep their separate worksheets, but add a worksheet that combines them all and run your metaanalyses on it.
    -John ... I float in liquid gardens
    UTC -7ąDS

  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: Excel Formula (97 SR-2 (k))

    The only problem with this approach is keeping the duplicates "synced" up so they are both always up-to-date. I have found with databases setup as essentially identical spreadsheets that putting them together and using data - autofilter so that they can STILL EASILY get it to look like the way they are used to PLUS has the added bonus of MUCH MUCH simpler analysis is a real win-win.

    Data autoFilter seems (in my experience) intuitive enough that even the "pointy-haired bosses" can easily understand how to do 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
  •