Weekly Summary and monthly summary

[minesh]

Dear Sir,

Greetings!!!

Can some help with the attached spreadsheet.I need the following calculation to be done in the spreadsheet.

1. Weekly summary of inventory of opening and closing oil available, total liter oil collected, cash paid against oil purchase, opening cash in hand, total cash received and closing cash available, Total oil collected and cash paid per person during the week.
2. Monthly summary of oil collected, inventory of oil, total oil collected from each person per month.

I tried to do using pivot table but each time i update data it needs to be refreshed. Any alternative using Vloop up function.


Really appreciate if someone from the group can help me out.

Thanks in advance

Regards
Minesh

 

[GraH - Guido]

Some suggestions...
Opening stock, Opening Cash could perhaps be meta data variables. So you can make week tables and month tables where every cell is a calculation.
For the 2 columns Oil received and Cash Paid for Purchase, you can use SUMIFS.
See File...
- I did a sample in the week summary.
- Also added a drop down to enable week selection.
- Month can use the same formula, by referring to MonthRef column.
- I added Week (ISO) and MonthRef to your data.
Would this be workable for you?