Forum Discussion
Saving reference down a column as its reference is changed
Hello, I would like to calculate total sales amounts by simply typing the number of sales, and using a selector box to determine the product being sold, and thus the amount made on the sale. My pro...
Another approach to this is to use named tables to ensure that the validation lists grow as more priced items are available and to ensure that new sales rows include all relevant formulas.
To convert a range of data to a table, you can use Ctrl+T. You can then go to the Table Design tab and on the far-left of the ribbon, give the table a name.
I created two tables:
1. sales
2. prices
The sales table is where I record the items and quantities sold. There are formulas to lookup the correct price and calculate the line item amount.
At the top of the sheet, there's a formula to calculate the sub-total of the sales lines.