Hi guys, I am wasting my time doing a job manually that is should be possible I would have thought to automate.
However, my knowledge of Excel doesn't extend to this so would appreciate some advise if you can spare the time
The data base:
I have a spreadsheet that interrogates sage and returns a list of all the parts on all the purchase orders for 2012.
This list includes parts complete and those yet to be worked on.
There is a column H which shows the due date for the part as indicated in the attached image.
There is also a column M as indicated that shows the total value of all the parts once they have been delivered to stores.
Column M shows as a zero until the parts are received by our main stores.
What do I need to achieve?
As part of my KPI reporting I need to report on my weekly labour, my weekly throughput and running total of each.
What am I doing at the moment?
I am manually selecting all of the parts within the two dates relevant for the week number and noting the sum for that week in £'s
I then put this into my spread sheet as the throughput for that week.
This can take around 30 minutes as it is a large database.
What do I need
I have created a basic spreadsheet with a column that lists week 1 to week 52.
I would like a formulae that I can put into the adjacent column.
This formula would look at column H on the database and return the sum of all the values in Column M between two dates.
The two dates for week 1 would be: 2nd January to 8th January for example
I could put two columns at the side of the week number column if that helps for the start and finish date for each week.
Sorry for the long post guys but hopefully if you haven't lost the will to live and topped yourself you can assist me with this
Sincerely
Martyn
Bookmarks