Automatically (dynamically) consolidate data onto same sheet

[tammorice]

Hi everyone

After scouring the Forum for answers, I have come up blank.

Can I please pick your more experienced brains on this one?

I need to consolidate daily data into weekly data onto the same sheet. Each day data is entered into the sheet and needs to produce a weekly total of product and hours at the bottom of the sheet.

I don't seem to be able to consolidate dynamically on the source sheet.

Is there a workaround for achieving this?

Thanks
Tam

 

[SpillerBD]

I wouldn't summarize at the bottom of the sheet as that is either way-down or needs to be positioned dynamically.
I recommend summary at top. Your Data should be converted to a Table. Use Ctrl+T or Insert\Table.
Use the SUBTOTAL function instead of SUM
=SUBTOTAL(9,TABLE RANGE)
this will give the sum of the filtered results.

 

[tammorice]

Thanks SpillerBD. Unfortunately I cannot convert the data to a table due to the layout.

I may need to completely redesign the timesheet.

 

[SpillerBD]

Mike (ExcelIsFun) doesn't stress the value of thinking all data in "Proper Data Sets," which are convertible to Tables, enough.
For the people I work with, I try and make that an important point and then use the Table techniques or Pivot Tables for Reports.

 

[tammorice]

This is good advice and i will definitely be changing the way i work my sheets.

 

[tammorice]

What if had a button that consolidated the data onto the same sheet when clicked? Is this possible?

 

[SpillerBD]

Dynamically, formulas on Table ranges are best... cough cough. But you say that's not a possibility at this time....
Pivot Tables are my preference for the flexibility and filtering, but changes to the source data require a Pivot Refresh. When the source range is a Table the source range is dynamic. You can use the entire columns if they all have headers and Filter Out Blanks.

Not being able to use a Table means updating the source range for Pivot Table with each Refresh manually. A formula route would be using SUMPROUCT. You can use column referencing and ignore blanks by criteria, not ideal but it works. SumProduct could be driven by criteria based on a Date calculation so it always shows the past seven days, past 7 business days (WORKDAY)