checking to see if certain cells sum to 24 hours

  • I have a spreadsheet in which there are two columns that I want to perform a check on. The spreadsheet is an input page where there is a product column and a column for the number of hours the product stayed in the tank.

    The input sheet is just for 24 hour analysis, therefore for each product the number of hours has to sum to 24.

    For example,



    The above example is correct, each product's hours sum to 24.

    I would like a way to check that each product's hours sum to 24. If they do not equal 24, I will let my macro display a msgbox prompting the user to go back and correct the mistake. So, I guess somewhere there needs to be the sum of the hours of each product (I'm not sure how to go about it)

    How could I accomplish this (without a macro)?

    I have attached an example of my problem below. The cells in the product column have pull-down menus which display every available product.

    Please Help!

  • Here's an attached example of a couple of pivot tables..... one just takes your product numbers and sums up the hours

    the second shows how you can further analyse your data - in the example, by weekday

    these are really basic pivots..... they can do so much more

    right click inside one and choose the wizard, you'll soon get the picture

    change some scource data, rick click inside again and choose "refresh data" and you'll see how the pivot changes to reflect the new info

    not too sure of how you can build this into your macro, although if you record refreshing, it should give you an idea - or there's always our VBA people here

    in terms of speed and less fuss, I agree that Paddy's pivot suggestion is probably goign to be the best

    I'd also recommend the help files on pivots - from memory, it's one area of the help files that are pretty good : I think there's even a decent tutorial in there somewhere


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!