Updating cells by more than 1 when copy/pasting or dragging across

[jshaw82]

Hi can anybody help, this is probably a really easy one but I can't find an answer.

In the attached file there is a table in sheet 2 that links to a list of items in sheet1 - sheet 1 will be the raw data and sheet 2 will be where I want it to look in a particular way. Copying and pasting the cells down is no problem as they go up by 1 but in the next column I'm going from the bottom cell of the previous column. Because I've got a lot of these to fill out I was wondering if anyone knew and easy way/formula that will allow me to drag row 2 all the way along (so basically it looks at sheet1, cell A6, A11 and so forth.

I've done it manually for the rest of the table but I've shown you what happens when I drag B1 across I just get "a"

test1.xlsx

Many thanks,

John

[Andrew-R]

Try this formula in B2, then copy across and down:

=OFFSET(Sheet1!$A1,(COLUMN()-1)*5,0)

[jshaw82]

Hi, this works, for cell B2, but when dragging across it keeps the same formula

[:) Sixthsense :)]

Andrew-R solution is working fine for me. Can you please attach a screen shot?

[Andrew-R]

It should keep the same formula, but the values it returns will be different.

If they're not then, first off, try pressing F9 to recalculate the sheet.

[jshaw82]

Thanks guys just needed to press F9!