incrementing cell in a formula

Microsoft Excel 2003 (full product)
April 19, 2010 at 21:48:22
Specs: Windows XP
In sheet2
A3 = Sheet1 F2
A6 = Sheet1 F3
A9 = Sheet1 F4

How do i create a formula to do the above and copy this to a large range of cells

See More: incrementing cell in a formula

Report •

April 20, 2010 at 05:36:35

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


Remove the $ signs:

Repeat 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:

Note that the $ sign is required
Copy cell A3 and paste it to cells A6 and A9

Now 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.


Report •

April 21, 2010 at 00:21:17
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.

Report •

April 21, 2010 at 05:19:30

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.


Report •

Related Solutions

Ask Question