invoicing system

Bobhope84

Bit of a long one so please bear with me…..

I'm trying to assist a family member with automating their small courier business driver invoices etc

So I have built a work book in excel what calculates and creates invoices based on numbers put in on the work book master sheet, but the data inputted comes from screenshots sent to a group chat and is manually entered. Plenty of scope for errors. Me and my big mouth suggested smartsheet would solve all of that.

So I've made a form drivers fill that out and send it, it populates the sheet and I have calculation columns to work out wages owed. and I can filter by name and between dates for pay periods (or run a report) but my calculations don't just look at filtered data and if I summarise a report I need to do a sum of the summaries to get the total.

Only solution I can see is a filter data sheet then copy and paste to a sheet set up with calculations already.

Question is am I missing something here of is that the easiest way and it needs some manual intervention?

Thanks for taking the time to read

Matt Lynn-PCG

@Bobhope84 (love the name)

Yeah it sounds like you're getting into pivot type data. If those filters are standard selections you could create a helper column by whatever you call those selections and then in your report group it by that helper column, then summary that group. If that don't work I agree I think you're looking at making a sheet more advanced. I have done a drop down(s) in the sheet summary to drive a helper column based on the drop down selections (which act like filters), that helper column says "X" or something based on the drop down selections. Then put another field in there that totals/sums/average etc. all the "X" or "Y" columns etc.

Paul Newcome

Can you provide some sample screenshots of what you have thus far and possibly some mock-ups of what you are evetually wanting to end up with?

Bobhope84

https://community.smartsheet.com/discussion/comment/466020#Comment_466020

Ok so first Pic is of the raw data that is collected from the submitted forms and then formula columns to manipulate to get the figures I need. to the right you can see sub totals and deductions etc.

The second image is a report that has been summarised and I'd like that to be used as a Wage slip that can be emailed to the driver to show their earnings for the month with deductions shown. If i could do a sum of the sums it would be fine but you cant do formula on a report, so that takes me back to copying and pasting filtered data to a sheet

Andrée Starå

https://community.smartsheet.com/discussion/134326/invoicing-system

Hi,

I hope you're well and safe!

Have you explored using the grouping feature in the report? You could group it by driver or similar and then the sum would also show on that level.

Make sense?

Would that work/help?

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Awesome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!

Bobhope84

https://community.smartsheet.com/discussion/comment/466016#Comment_466016

Thanks for the insightful and helpful comment. Having helper columns and a total payable by row will have a total payable by month when summarised

Matt Lynn-PCG

@Bobhope84 Cool… glad that helped. Let me know if you need any support putting that together!