Hello,
I have listed the paints need for the job in the Invoice Template sheet Descriptions. Some of the paints are needed for different applications (see Spread Rates & Cost sheet).
The Material sheet lists the quantities for each application.
How can each paint color / type quantity be determined with formulas on the Invoice Template sheet?
Thank you
I was looking at the Invoice Process and to my mind there are several things to be considered.
First, there is no correlation in "Material" with any paint product i.e. which paint is being used and hence no link to "Spread and Material costs".
I would consider consolidating all material infomation into a single Product DB and then adding Product details to Job (Order) details,
Re your immediate question, Quantity (in gallons) is Total Area to be covered divide by Coverage, rounded up to nearest gallon or smallest unit avaiable.. Consider also room might require different colours on different walls so your Job details (IMHO) need to more "granular"..
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
I agree that there needs to be more choices of colors per room. Perhaps on the Measure sheet Each room wall colors can be selected.
The multiple sheets for calculating seem to work to arrive at the estimate but it seems that it would be more stream lined if it can be done on the Measure sheet without having to input in other sheets to arrive at the estimate.
I will give it some more thought.
I do see how to indirectly get results for now.
The colors are chosen per application on the Spread Rates & Cost sheet.
The Budget sheet has the quantities of paint per application.
So can formulas arrive at the results needed for quantities?
6.0
2.0
4.0
I think this is the issue and hence my suggestion to consolidate some of your sheets as there is fair amount of data repetition. I find looking through too many sheets tedious and therefore find it difficult to get a consolidated Job overview.The multiple sheets for calculating ........
This doesn't seem like a simple task but I will try to consolidate.
Re: post #4: the "Measure" sheet also has the Sq, Ft data (repetition again!) so you can estimate paint volume by dividing Wall Sq Ft/ Coverage per volume
In UK, it is usually coverage per litre with a "typical" can of paint being 2.5 litres (approx 0.5 gallons) so 5 gallons covers acres!!!
I would go back to the drawing board (literally with pencil and paper!)and think through your tables (DBs) to avoid having to later rework formulae/VBA.
The only sheet that needs to be changed I think is the Spread Rates & Cost.
This should be the paint database of paints that are used on jobs. These colors may be used for other jobs so one place as a resource.
The measure sheet could then choose the paint for each room for 3 categories: Walls, Ceilings, the 3rd category will be for Trim, Baseboards, Doors, Door Trims, Crown Molding, Chair Rails.
So far I have these changes:
I added sheet Paint DB to enter paint details.
I inserted 3 columns in the Measure sheet to make separate paints for each room.
Okay I went back to another way for calculating paint estimate. This has fewer sheets and seems to get the job done.
On the Invoice Template sheet how can the gallons of paint needed (rounded up to next gallon) for each paint from the Job Details sheet with formulas?
I figured out one possible formula:
Formula:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks