Change cell references in formulas

[ozhunter]

Hello

I don't even know if this is possible, I have been unable to work anything out that even comes close.

I have a form that is populated by formulas that look at another sheet (sheet1).

My form has five "areas" that consist of six rows. Each of these areas appears on one printed page 5 times. There are about 15 or 20 formulas in the area, looking for data in non sequential cells on sheet1

Everything is rosey until I try to get the formulas to look at the next row of data on Sheet1

If you copy and paste the formula, it changes the cell that I am looking in by 6 rows instead of one, because I have moved it six rows.

ie the active cell formula is ='sheet1'!a1

when I copy and paste, it ends up as ='sheet1'!a7, when it should be 'sheet1'!a2

Is it possible to restrict the cell reference to changing by one row, when it is moved 6?

[Pete_UK]

Use this formula in the first cell:

=INDEX('sheet1'!A:A,INT((ROWS($1:1)+5)/6))

which will give you the equivalent of sheet1!A1. However, if you copy it to the cell 6 rows further down it will give you the equivalent of sheet1!A2, and so on.

Hope this helps.

Pete

[ozhunter]

Thanks Pete

How do I make your formula start at a2?

[Pete_UK]

You could do this:

=INDEX('sheet1'!A:A,INT((ROWS($1:1)+5)/6)+1)

Hope this helps.

Pete

[ozhunter]

Holy cow. You are the man. The formula you have given me works fantastically well. I didn't think I would be able to get this part of my little task to work. Thank you Pete.