Hey folks,

We are a lawn and landscape maintenance company. We have a list of common services that we perform for our customers. There is a weekly service that occurs every week through the growing season and then other services that occur as needed or on to a different schedule.

Previously we have printed a weekly route sheet for each truck with every property and when the property was serviced, the crew leader would write down the date, product service code so we would know which services were performed on that date, and initials. Obviously this led to a large pile of ratty, service truck clip-board papers with cruddy hand-writing that I have to compile and organize at the end of each month, and then go through and manually compile all the data for every property. Huge undertaking and will only get worse as we grow.

We looked at having software built to meet our needs, but it is beyond our budget at this point. We do however need a more effective and efficient method for keeping track of what, when, and where we performed services at the end of each month.

We have decided to use Excel and then convert these into Google Sheets and so the files are kept online and are accessible by management and crew leaders via iPad or iPhone in real-time. The advantage is the real time accessibility, real time to go in an add notes for each property, make route/order changes, etc.. Much easier than a list that's printed at the beginning of the week each week. The other advantage is that the sheet can total up the number of times we serviced a property.

We currently have two trucks. We offer weekly or bi-weekly mowing schedules, so each truck has two different routes, week one and week two. We call these cycles. Some lawns are serviced every week and others are every other week - hence the need for two weekly routes. The weekly routes are of course divided into 5 days. So what we have is 4 workbooks, Truck 1 Cycle 1, Truck 1 Cycle 2, Truck 2 Cycle 1, Truck 2 Cycle 2. Each book has 5 sheets, one for each day of the week. On each sheet vertically is the list of properties for that day, and horizontally there are 5 columns to the right where we enter the date serviced. We have 5 colums for 5 weeks in a month. (Gotta count partial weeks) Then to the right of that is a "total" column which sums the number of cells in that row that have a service date entered.

So I get a total at the end of the month for each property, but I have two workbooks per truck which means I have to combine the two books to get the actual number of visits for the month. And the other problem with this is that I don't know what services were performed, I only know the dates, so we have to keep a separate list (currently on paper) of what services were performed at each property.

I would love it if we could have one workbook per truck that the crew leader could select which services were performed that week and at the end of the month I could have a tally sheet that would tell me which services were performed that month and how many times each of those services were performed.

Any ideas? Is this too much for excel?