formula series (newbie)

[Guest]

Hi,

In ( ) are the ref cells in sheet2 that I've entered the formula manually
sheet 1
A B C
1 (m125) (m126) (m127)
2 (n125) (n126) (m128)
3 (o125) (n127) (m129)

I want to avoid entering all the formulas manually.

when I drag and fill the formula down ='sheet2'!m125 I get

1 m125
2 m126
3 m127

when I drag and fill the formula across ='sheet2'!m125 I get

1 m125 o125

what am I doing wrong?

 

[Ragdyer]

You're not doing anything wrong ... that's exactly the way that XL
increments cell references when formulas are copied across columns and down
rows.

I don't know exactly what you're looking for though!
I believe you've got some typo's in your example.

You're showing Column M to go along Row1, and have the rows increment as
they cross the columns.

Row2 and Row 3 examples don't match the pattern of the Row1 example.

Are you looking for Row2 to have the same pattern with Column N,
and Row3 to have the same pattern with Column O?

In other words, have Row125 in Column A, 126 in Column B, 127 in Column C,
.... etc.
And Column M in Row1, Column N in Row2, Column O in Row3, ... etc.

If so, try this formula:

=INDEX(Sheet2!$M$125:$Z$250,COLUMNS($A:A),ROWS($1:1))

Copy across and then down.
I set the boundaries of the range to be copied from M125 to Z250.

Also, this formula can be entered *anywhere*, and will still return the
referenced range as it's copied.

 

[Ragdyer]

Then the formula I suggested *will work* ... BUT ... *only* within the
boundaries of the range that the formula indexes.

Don't forget, that the range in the INDEX() is *relative*, and referenced as
1st row, 1st column by the actual cell locations of the referenced range.

Can you revise the formula to your actual needs?

If not, post back with *exactly* the range of cells you wish to reference.