Re: formula series (newbie)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I did have typos. Let me try this again. In ( ) are ref to sheet2
A B C
1 m3 m4 m5
2 n3 n4 n5
3 o3 o4 o5

I just want to drag and fill the reference formula down from row 1 to row 3.
My data in sheet 2 is not in the same row/col for every month. If I can't
drag and fill I'll ref the cell in sheet 2 manually.

Thanks for you help.




"Ragdyer" wrote:

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Sam" <Sam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9C6D192B-87E9-43AC-B5C7-74B435D094BD@xxxxxxxxxxxxxxxx
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?






.

Relevant Pages

  • Is this Vlookup
    ... columns that I need to reference. ... Col_B has old ref and Col_Z has new ... While on Sheet2 I have Col_K which is the same as Col_B on shee1 (old ... Ref ie same as Col_Z on sheet1. ...
    (microsoft.public.excel.misc)
  • Re: Passing arguements by reference
    ... but doSomething doesnt change p unless i use "ref"? ... reference, hence both p and q pointing to the same object, not to different ... don't use the ref keyword. ... void Execute() ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Documentation suggestions
    ... > Ian> I think it would be very useful if there was reference (not just ... Lang ref as only for "language lawyers". ...
    (comp.lang.python)
  • Re: pass by reference
    ... focus has moved from insisting to use "by ref" in java-context ... independent of any other use of the word "reference" in a language. ... Just because Java-refs can be re-targetted by assignment? ... If Java had an operator to tell an Object ...
    (comp.lang.java.programmer)
  • Re: ref parameter
    ... It's the same as in Java if you leave out the 'ref' - you can modify the internals of the object, but not the top-level reference. ... Instances of value types are passed as a copy by default, you can use the "ref" keyword to pass a reference. ...
    (microsoft.public.dotnet.languages.csharp)