how to add a price list to an invoice in google sheets
IN SUMMARY
To add a price list to an invoice in Google Sheets, you can create a drop-down list with your product/service items and prices. Then, use the VLOOKUP function to fetch the corresponding prices based on the selected item in the invoice.
Create a Price List
This sheet will serve as your price list. In the first column, list all your products or services, and in the second column, enter their respective prices.
Ensure that the product/service names are unique and that the prices are formatted consistently (e.g., with currency symbols or decimal places).
To prevent accidental changes, you can protect the price list sheet by going to 'Data' > 'Protected sheets and ranges' and following the prompts.
Create a Drop-down List in the Invoice
In the invoice sheet, highlight the cells where you want to select the products or services from the price list.
Go to 'Data' > 'Data Validation' and select 'Criteria' > 'List from a range'. Then, select the range of product/service names from your price list sheet.
Under 'Data Validation' settings, you can choose to display the drop-down as chips or an arrow, and set options like showing a warning or rejecting invalid entries.
Fetch Prices Using VLOOKUP
In the cells where you want to display the prices, enter the VLOOKUP formula: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Replace 'lookup_value' with the cell reference of the selected item, 'table_array' with the range of your price list, 'col_index_num' with the column number of the prices, and set 'range_lookup' to FALSE for an exact match.
To handle errors (e.g., when an item is not found in the price list), wrap the VLOOKUP formula with the IFERROR function: =IFERROR(VLOOKUP(...), ""). This will display a blank cell instead of an error message.
Use formulas like SUM to calculate the total cost based on the fetched prices and quantities entered in the invoice.