Saving reference down a column as its reference is changed

[Kyle_P19]

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 problem is that when i change the product being sold, it changes the amount of past sales as well.

I've included a small file as an example.

I'd like to enter a new number to cells D6:D10 as sales are made, and select the product being sold at the time through the drop down list in E1.

Then I'd like the price of the product to be automatically entered in column E6:E10, and remain even after selecting a new product in the drop down box.

Any code or design suggestions are greatly appreciated.

Kyle Parenzan

[#2]

@Kyle_P19 

See the attached for one solution.

You just needed to make the drop down part of the row in which the calc took place.  And assuming your real world application has more than two products, use VLOOKUP rather than your IF formulation to determine the per unit price.

[#3]

bonjour, quand je voulais imprimer et convertir en pdf, je ne vois pas la page entier, sur exel,

[#4]

@mathetes 

This is the right solution to calculate line-item totals providing the intention of Number of Sales is "units sold" and not "line item / row number" (it's not clear to me which is intended tbh).

Additionally, I think @Kyle_P19 was looking for a running total in the Total column, but a quick adjustment in the first row of the total column to this:

=SUMPRODUCT($D$6:D6,$E$6:E6)

and then dragged down to the range of the cells, would achieve that.

[#5]

@Kyle_P19 

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.