Proper format

[Thanek]

Hi,
I’m trying to find the appropriate format for the following shared spreadsheet.

1. I need to be able to filter by different fields, primarily Location or Month, without breaking the timeline or breaking the subtotals and grand total.
2. I need to be able to easily add or remove SKU’s without breaking the table.

I would like to fuse filtering and grouping but I am having issues saving the structure of formulas totaling and subtotaling. Ideas?

https://www.dropbox.com/s/jk5uhahg4ykj8kn/Project Schedule.xlsx

 

[Col Delane]

Hi Thane

I'm not sure what you mean by each of the following:

... without breaking the timeline or breaking the subtotals and grand total.

... without breaking the table.

I would like to fuse filtering and grouping ...

Note that the current subtotals for January don't compute (only sum last two rows!)

As a result I'm not sure whether the following will be acceptable, but it is how I would work with data like you have:

• delete any rows that do not contain SKU data (including "Month" rows like 5 & 12, current totals)
• ensure that the values entered in the Date field (column C) are proper Excel date values rather than text
• add a new field/column which contains the following formula in each data row: =month([Date cell ref]) to return just the month number of each record

The above will result in a clean and simple database with no blank rows, where every row represents a data record, and that can be easily manipulated / analysed - which is difficult to do with your current 'segmented' structure.


Then:

• apply Autofilters to the header row (3)
• sort the database by Date or Month
• apply Automatic Subtotals (which will also add a Group / Outline) to the data base, changing on Month and with "Summary below data" checked.

HTH