I have a spreadsheet with 3 critical columns "date", "part", "defects" and "length" and I would like to be able to add to this spreadsheet while keeping running tallies of the total number of defects and total length sorted by month and/or part #. I have been using the sumif function to sort by part #, however sorting by month is not as easy. Do I need to reference the dates as serial numbers? Can anyone explain how to do that? I would like to be able to add entries and have my monthly and part report tables updated automatically. Thanks in advance.

Hve you looked into using pivot tables?

I fiddled with a pivot table, and I think they will be helpful. Will the pivot tables update when a new line of data is entered? How can I make sure the data range for the pivot table always contains all of the data? Also, is it possible to perform a calculation based on these pivot tables and have that update as well? for instance I have a pivot table that is displaying the sum of defects and the sum of footage for every part number, but how can I calculate the footage per defect and have it update?

Also, as far as my report by month, I have several data inputs throughout January for instance, but I would like to get a macro view of January as a whole. How do I accomplish that in a pivot table?

Hello,

it really sounds that Pivot Tables should be the right tool for this. if you need to ensure all your data are insode the pivot cache, use dynamic ranges:

For example:
earlier discussion

Start the pivot wizard and assign the data range as "=YourNamedRange"

Martin

1. Updating Pivot table. You will need to refresh the data to have the pivot table update. This can be set to refresh on open (pivot table setting), manually (right click on the pivot table and select refresh), or via VBA.
2. You need to update the formula to include extra rows in most situations, or you can use dynamic named ranges. You can do a search on dynamic named ranges in this forum, or if you post your range, someone can post the formula for you. To create a dynamic named range (aside from the formula syntax), Insert (menu)->Name->Define... will bring up the dialog box. Then when setting your pivot table range, you would use the named range instead of the fixed range, i.e., $A1:$C342.
3. You can create calculated fields in your pivot table and those will update; NOTE: your calculations will be based on your totals, not per individual item in your data set. In question above your formula would be =defect/footage. To create a calcuated field, right click on your pivot table column, select formulas, then calculated field to get the dialog box.

in your next post, you ask about grouping by month.
To group, have your date field selected, right click, and select group and outline and you can choose month/year.