Microsoft Excel 2003 (full product)

Eg

In sheet2

A3 = Sheet1 F2

A6 = Sheet1 F3

A9 = Sheet1 F4How do i create a formula to do the above and copy this to a large range of cells

Hi, You don't said how you want the three linked cells A3, A6 and A9 copied to the other cells on Sheet2.

Anyway here is a start - you can come back with more details on what you want to achieve when you have had a look at these.

Method 1

Select cell F2 on Sheet1 - right-click and Copy

Select cell A3 on Sheet2 - right-click and PasteSpecial...

Click Paste Link

Cell A1 will now contain=Sheet1!$F$2

Remove the $ signs:=Sheet1!$F$2Repeat this for the F3/A6 and F4/A9 pairs.

Now select cells A3 to A6 and drag them one column to the right and now cells B3, B6 & B9 will be linked to cells G2, G3 and G4 on Sheet1

Method 2

If you want to extend downwards, try this:

In Cell A3 on Sheet2 enter this formula:=OFFSET(Sheet1!F$1,(ROW()/3),0)Note that the $ sign is required

Copy cell A3 and paste it to cells A6 and A9Now select cells A3 to A11 - Note that selecting the empty cells A10 and A11 is required for this to work.

Drag to extend the selection downwards, stopping at a cell A20

Cells A12, A15 and A18 will now be linked to cells F5, F6 and F7 on Sheet1(The function ROW() in these formulas returns the row number of the cell containing it - when divided by 3 it is used to provide a row offset value from the base at cell F1)

Hope this gives you some ideas to get what you want.

Regards

You're a genius thankyou so much. i used method 2 and it worked a treat. i dont really understand how OFFSET works but it functions perfectly.

Hi, Glad to hear that it worked.

OFFSET() returns the value from a cell based on the 'distance' from a base cell. In the example the base was cell F1 on Sheet1.

OFFSET() uses two 'distances', the number of rows and the number of columns.

In the example the number of rows away from the base cell was calculated from the row number divided by 3 and the number of columns was set to zero.

=OFFSET(A1,1,2) would return the value in the cell 1 row and 2 columns away from cell A1, which is C2.

If the base cell is not in row 1 or column 1, the offset values can be negative. =OFFSET(C2,-1,-2)

If you use a very large number of OFFSET formulas you may see a slight slow down in Excel, as the function is one of a group that are referred to as volatile - this means that they recalculate every time there is a change anywhere on the worksheet.

Regards

Ask Your Question

Weekly Poll