Complex lookup and report data wanted
Good morning all. I have a complex procedure I want to try and accomplish.
I have 2 sheets currently created. 1 is for our main invoice for a job, that has a payment schedule and a checkbox colum if the scheduled item has been invoiced.
I have a second sheet that allows us to input all of the change orders, their amount and it totals them in a total column at the top, and that total column is then linked to the original invoices sheet. I have attached a screenshot of both.
What I want to achieve.
We have to report weekly to our supervisor what we have invoiced for the week, so they keep a running total.
I wanted to create a report that would look up todays date (Which we would use as each friday as the base date for counting back) and check this current week if we have invoiced anything.
My thought process is, if I create a metric sheet, and that metric has a formula that checks the invoice sheet.
It checks what todays date is, then checks the week from monday to friday, looks at the check box to see if the check box has been checked, which would indicate an invoice has been sent this week, then it would report how much that invoice was and sent out. It would also check if that date was greater than monday (so lets say we invoiced something 10 days ago) it would see that it has already been invoiced and it wouldn't display that date in the report.
I am pretty beginner level at these formulas and stuff, so let me know if you think there is a better way to achieve this?
Help Article Resources
Check out the Formula Handbook template!