Specifying a Row number value in a cell.

[smithgt]

I have a formula that I want to copy from sheet to sheet.

The problem is that I have a defined range of A3:Axxx where xxx is a row at the bottom of the data.

On Sheet1 one xxx is 1056, on Sheet2 xxx is 305.

To copy the formula I was thinking of putting the row number in a specific cell. e.g. K1068 = 1056

Is this possible?

The second, related question is how do I reference a cell in relation to another? As K1068 wont always be K1068, bit it will always be two cells below the cell thats looking for it.

 

[Richard Schollar]

Hi

You can use INDIRECT to generate a range:

=INDIRECT("A3:A" & $K$1068)

will generate a range from A3:Anumber_in_K1068

Use in a formula like

=SUM(INDIRECT("A3:A" & $K$1068))

You can also use INDIRECT refer to a cell 2 below the one in which it is entered by using R1C1 notation with it:

=INDIRECT("R[2]C",FALSE)

will always point to the cell 2 below the one in which it is entered.