Forum Discussion
Issues with IFs And Formula
I am open to a having a all the daily reports on one sheet and the second sheet to show the monthly total.
Chris
Here (attached) is a start, using a single database for all individual days, and a single formula copied across the monthly summary. That formula takes advantage of the FILTER function, which does require a new version of Excel.
I only did the first four days of data, but that should be sufficient to demonstrate the process.
The formula is this:
=FILTER(Table1[Delivery Status],(Table1[Date]=Monthly!F$2)*(Table1[Accounts Name]=Monthly!$D3),"")
And what that does is filter the column containing "Delivery Status" and show the result where
- the date matches the date in row 2, at the top of the column (F$2 accomplishes that; changing to G$2, H$2, etc., as you copy the formula across all of the monthly dates)
- the account matches the account in column D ($D3, which retains the D but increments the row number as it gets copied down, so covering any number of accounts)
This does assume that there's no more than one delivery per account per day.
Here's a good reference for learning the FILTER function. https://www.youtube.com/watch?v=9I9DtFOVPIg
- One last question is that I would need to Self Populate the began and end times depending on what account is listed with out manually putting them in each time. What would be the Formula for this?
- I was assuming that the daily data was already under control, since you mentioned something about importing GPS data or something like that......If the begin and end time for each location is always fixed, then you could use a table and a VLOOKUP or something like that to populate the daily data from that other table. If that doesn't make sense to you, then please come back with a more complete description of how that is working now.
- This is Great Thank you so much for your help
Getting a Spill Error for the hours to began
You need to get acquainted with all of the many ways that Excel can look things up in tables. Probably the most basic function is VLOOKUP, which I've used here. There are more sophisticated ways, XLOOKUP, INDEX and MATCH....among them.
If you make the table larger, you'll need to modify the size the the referenced range in the formula, but as with the earlier example, this gets you started.