How can I copy an array formula down the row and maintain the row number?

[Kawinga]

This is what I'm trying to achieve,
=INDEX('MASTER GRADES'!$C$8:$J$5003,SMALL(IF('MASTER GRADES'!$C$8:$C$18=$J$5,ROW('MASTER GRADES'!$C$8:$C$18)),ROW(1:1))-7,3)

I want when I copy the formula above which has 11 rows into another section within the worksheet, the row number should start again with ROW(1:1)...and not ROW(12:12)...

I hope being my first post, this makes sense? I'm new and young in excel

Thanks

[Richard Buttrey]

If I understand the request just make ROW(1:1) an absolute ROR($1:$1)

[Kawinga]

Your assistance will be greatly appreciated. I just don't if that's doable. Thanks

[Kawinga]

Hi Richard,

I don't want ROW(1:1) to be an absolute. I intend to multiply a section of several rows without changing the row numbers of the new section which I just copied into. I have ROW(1:1) to ROW(11:11) now when I copy this section and paste it below ROW(11:11) it starts with ROW(12:12) which forces me to format the row numbers again to ROW(1:1) - ROW(11:11) and so on...

Thanks for your quick response

[TheN]

If I understand you correctly, this should work:

Copy the formula from the formula bar, instead of the cell itself. Then paste it as needed. That will prevent it from changing the formula relative to where it is pasted.

So, go to the first instance, and copy that formula. Alternatively, you could have the formula somewhere else, proceeded by ' which tells excel it is text. This will allow you to copy from that cell value instead of needing to manually click into the formula bar.

Hope this helps

[Kawinga]

Yea! You rock it TheN. That perfectly worked. Thanks so much. You have a great one.

[Aardigspook]

You could also replace your ROW(1:1) with this: MOD(ROW(1:1)-1,11)+1
Dragging that down will count to 11 then restart at 1.

Edit: unless you particularly want to use Row, you can alsoget a repeating sequence of 1-11 by replacing your ROW(1:1) with: IF(A1=11,1,A1+1)
This assumes that your formula is starting in A2 - amend it as necessary.

[Kawinga]

Thanks Aardigspook

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[xlnitwit]

Hi Richard,

I don't want ROW(1:1) to be an absolute. I intend to multiply a section of several rows without changing the row numbers of the new section which I just copied into. I have ROW(1:1) to ROW(11:11) now when I copy this section and paste it below ROW(11:11) it starts with ROW(12:12) which forces me to format the row numbers again to ROW(1:1) - ROW(11:11) and so on...

Thanks for your quick response

I can't help but think that you misunderstood Richard's suggestion. If you amend your existing formulas so that they use ROW($1:$1) in the first row and ROW($2:$2) in the second row and so on (or even just use 1, 2, and so on), then when you copy the formulas elsewhere they will be what you want. Admittedly you have to edit a few formulas to start with but that is not particularly time consuming.

[Kawinga]

That now explains and solves the whole puzzle. Thanks so much good people.