Make a formula increase the row number

Microsoft Microsoft office excel 2007 ac...
September 8, 2010 at 20:46:27
Specs: Windows Vista
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:


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?

See More: Make a formula increase the row number

Report •

September 8, 2010 at 20:56:29
Sorry for the double post, but here are some screen shots of the worksheets so that you understand better the situation.

Report •

September 9, 2010 at 04:11:05
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)

Report •

September 9, 2010 at 08:20:34
It worked great! Thank you for the great help!

Report •

Related Solutions

September 9, 2010 at 08:35:02

Report •

Ask Question