If you are working in retail or manufacturing sales then creating and sending out price quotes is an important, but tedious part of your day. The good news is you can eliminate a major part of the work involved in this process by using some Excel magic!
Most vendors have a price quote or a price proposal template where they enter a product Id, the price and the amount of for each product requested. Usually that template will also produce the total price for the proposal automatically.
Today we will show you how to set up a template in Excel that will also automatically fill in the price of the products and save you the trouble of looking up the price of each and every product.
You can perform this magic by maintaining a product catalog (most businesses will already have one) and have the price quote template automatically look up the price from that catalog.
Here is a very simplified sample of a product catalog:
In our case, we are searching the area where the product prices are listed within the product catalog for a row whose first column is equal to A7 – which is the product name.
To put it in even simpler terms, we are looking for the row in the product catalog that contains ‘Cookies’ in column A.
Once the lookup function finds that row, it returns the second column in the area we specified.
The second piece of the magic lies in how we indicate to Excel where the data is found in another Excel file.
This is called a cell reference, and if you’ve ever written a formula, you’ve used references before. Whenever you wrote a formula like:
=A1
However, you might have not referenced a cell in another file or in another sheet before. Fortunately enough this is easy to do.
To reference a cell in another sheet but in the same workbook, simply put the sheet name followed by an exclamation mark before the cell name.
=Sheet2!B1
Means cell B1 in sheet2.
To reference a cell in another file, put the file name followed by the sheet name in square brackets followed by an exclamation mark before the cell name.
Note: If the filename or the sheet’s name contains a space then the filename and sheet’s name must be surrounded by single quotes.
='test file.xls[Sheet2]'!B1
Means cell B1 in sheet2 in file test file.xls.
And so, by referencing a range in the Main Catalog file…
'[Master Catalog.xls]Sheet1'!$A$3:$B$6
We tell the VLOOKUP function to look for the product price on another file.
Summary
We all know the popular phrase ‘time is money’, well in sales this is definitely something to keep in mind – the faster you can respond to price enquiries the more business you can deliver! Hopefully this Excel trick can help you work through more orders, and with less hassle than before.
P.S.
You can also check out if our PDF to Excel Converter can help you save time and improve your work process.
