CASE STUDY 5
Google Sheets - MASTER QUOTE
The easy way to Create Quotes in Google Sheets
Google Sheets is used by many in building all kinds of tables, easy or complex calculations but also Invoices, Purchase Orders and also Quotes.
When Creating Quotes in Google Sheets some information is critical but in general, we see people running up and down between different files or systems to gather details on:
- Updated Product Cost
- Actual Product Availability
- Profitability Calculators
The following file it addressing this by incorporating in one file:
- Quote
- Costs
- Inventory
And depending on your system capabilities, the Costs and Inventory Data can be imported automatically in Google Sheets from other files, allowing you to deploy a Quote Work File that will always be current (up to date).
This file is using a combination of VLOOKUP functions combined with several logical operators and conditional formatting to make the file easier to work with.
Registered members will SEE MORE and will get the link to the Google Sheets - QUOTE.
In this file, the main function used is:
=IFERROR(IS(ISBLANK(B19),,VLOOKUP(A19,’Cost File’!C:D,2,FALSE)),)
- We are searching for the product code in the Cost File and we return the cost of that specific item: VLOOKUP(A19,’Cost File’!C:D,2,FALSE)
- This operation will only be performed if the cell B19 is not empty (meaning that we introduced an item in the quote – for aesthetical reasons): IF(ISBLANK(B19 …
- If there is an error in the formula mainly due to data not found in the Cost File, the result will be BLANK, Having this set will allow the file to calculate the totals QUOTE (if you are adding cell where, between the values, you have at last one call with an error message), the entire sum will read ERROR instead of the total of the cells with values: IFERROR(…
- We do the same for the other sheets in the document – one sheet for each of the categories we have identified for our data.
This content is locked
Login To Unlock The Content!
