I am working on a worksheet and I need to calculate the total cost of the boxes the employees have sold from each product. On my ProductSales sheet, each item has its own column and each employee the same.

(Ex. Item1 is on D3, Item2 is on D4, Item3 is on D5 etc)

(Ex. Employee1 is on A3, Emp2 is on A4 etc)On the ProductData sheet now, there is a list the same way as on ProductSales that includes the number of boxes each employee has sold but there is also another Column with the Products that also includes the Price of each one.

(Ex. Item1 is on A2 and his price on E2, Item2 is on A3 and his price on E3)

I am using a formula on the ProductSales sheet but I cant make it increase the row number when I drag it to the right.

The formula I am using is:

=ProductData!$K2*ProductData!$E3

Now when I drag it to the right to autofill the cells it doesn't change anything.

I want to make that that way, that each time I drag it to the right it will make the row number in

ProductData!$E3 increase by 1.Ex. From =ProductData!$K2*ProductData!$E3 when its dragged in the cell to the right, become =ProductData!$K2*ProductData!$E4

Is there a way?

Sorry for the double post, but here are some screen shots of the worksheets so that you understand better the situation.

Try the INDIRECT and COLUMN functions: With this formula in Column C, which is also known as Column 3, it will be equivalent to your formula that references E3:

=ProductData!$K2*INDIRECT("ProductData!$E" & COLUMN())

Drag it to Column D (Column 4) and it will reference E4.

If you can't start in Column C (3) then adjust the COLUMN() function with a little math.

e.g. With this in Column A (1), it will also reference E3:

=ProductData!$K2*INDIRECT("ProductData!$E" & COLUMN() + 2)

It worked great! Thank you for the great help!

Anytime.

Ask Your Question

Weekly Poll

Do you think Samsung's Bixby will compete well against other phone AI systems?

Discuss in The Lounge

Poll History