Can you change the relative reference of a cell in an Excel formul
 

I have a formula in cell K4 which includes a reference to the value in cell
H11. When I copy the formula to cell K5 Excel thinks that I then wish to
refer to the value in H12.

I would like to be able to copy the formula in K4 down a varying number of
rows each time I do it and have the reference to cell H11 increase by 10 rows
each time.

For example the formula in K5 would refer to cell H21 and the one in K6 to
H31 and so on.

Is this possible in Excel?

Many thanks - Rod

Can you change the relative reference of a cell in an Excel formul
 

Try:
=INDEX(H:H,(ROWS(K$4:K4)-1)*10+11)


"Rod Bowyer" wrote:

I have a formula in cell K4 which includes a reference to the value in cell
H11. When I copy the formula to cell K5 Excel thinks that I then wish to
refer to the value in H12.

I would like to be able to copy the formula in K4 down a varying number of
rows each time I do it and have the reference to cell H11 increase by 10 rows
each time.

For example the formula in K5 would refer to cell H21 and the one in K6 to
H31 and so on.

Is this possible in Excel?

Many thanks - Rod

Can you change the relative reference of a cell in an Excel formul
 

In K4: =OFFSET($H$11,(ROWS($1:1)-1)*10,0)

copy down


"Rod Bowyer" wrote:

I have a formula in cell K4 which includes a reference to the value in cell
H11. When I copy the formula to cell K5 Excel thinks that I then wish to
refer to the value in H12.

I would like to be able to copy the formula in K4 down a varying number of
rows each time I do it and have the reference to cell H11 increase by 10 rows
each time.

For example the formula in K5 would refer to cell H21 and the one in K6 to
H31 and so on.

Is this possible in Excel?

Many thanks - Rod

Can you change the relative reference of a cell in an Excel fo
 

Many thanks for this. You would not believe how long I tried to work this
out!!

"JMB" wrote:

Try:
=INDEX(H:H,(ROWS(K$4:K4)-1)*10+11)


"Rod Bowyer" wrote:

I have a formula in cell K4 which includes a reference to the value in cell
H11. When I copy the formula to cell K5 Excel thinks that I then wish to
refer to the value in H12.

I would like to be able to copy the formula in K4 down a varying number of
rows each time I do it and have the reference to cell H11 increase by 10 rows
each time.

For example the formula in K5 would refer to cell H21 and the one in K6 to
H31 and so on.

Is this possible in Excel?

Many thanks - Rod

Can you change the relative reference of a cell in an Excel fo
 

Thanks - this works wonderfully!!

"Teethless mama" wrote:

In K4: =OFFSET($H$11,(ROWS($1:1)-1)*10,0)

copy down


"Rod Bowyer" wrote:

I have a formula in cell K4 which includes a reference to the value in cell
H11. When I copy the formula to cell K5 Excel thinks that I then wish to
refer to the value in H12.

I would like to be able to copy the formula in K4 down a varying number of
rows each time I do it and have the reference to cell H11 increase by 10 rows
each time.

For example the formula in K5 would refer to cell H21 and the one in K6 to
H31 and so on.

Is this possible in Excel?

Many thanks - Rod