How to make formula automatically increase by one?

[Tommy1005]

I'm trying to drag this formula across about 200 columns:

=INDEX($B$21:$BI$215,MATCH(C14,$A$21:$A$215,0)+2,2)

but for every column it gets dragged over I need that last number (which I bolded) to go up by one.

So it needs to be..
=INDEX($B$21:$BI$215,MATCH(C14,$A$21:$A$215,0)+2,2)
=INDEX($B$21:$BI$215,MATCH(C14,$A$21:$A$215,0)+2,3)
=INDEX($B$21:$BI$215,MATCH(C14,$A$21:$A$215,0)+2,4)
=INDEX($B$21:$BI$215,MATCH(C14,$A$21:$A$215,0)+2,5)
=INDEX($B$21:$BI$215,MATCH(C14,$A$21:$A$215,0)+2,6)

etc...

It really sucks having to do it manually, is there an easier way?

[zbor]

Try:

=INDEX($B$21:$BI$215,MATCH(C14,$A$21:$A$215,0)+2,1+ROWS($A$21:A21)/10)

[Tommy1005]

Same problem, doesn't work when you copy it across

[Tommy1005]

=INDEX($B$21:$BI$215,MATCH(C14,$A$21:$A$215,0)+2,COLUMN()-1)

worked for me =)

[zbor]

Same problem, doesn't work when you copy it across

Yes, sorry it was for rows... For column you need to use COLUMNS formula:

=INDEX($B$21:$BI$215,MATCH($C14,$A$21:$A$215,0)+2,1+COLUMNS($A$21:A$21)/10)

[ronmaltase]

Hello,
Can you think of any way to increase this day value in the middle of this formula?

it's the 1,2,3 value...

='O:\BSC\DAILY_OP_POS\DAILY OP POS TOOLS\[DAILY OP POS for 11.1.12.xlsx]OVERALL SUMMARY'!$B$13='O:\BSC\DAILY_OP_POS\DAILY OP POS TOOLS\[DAILY OP POS for 11.2.12.xlsx]OVERALL SUMMARY'!$B$13
='O:\BSC\DAILY_OP_POS\DAILY OP POS TOOLS\[DAILY OP POS for 11.3.12.xlsx]OVERALL SUMMARY'!$B$13

-thanks, Ron M.

[FDibbins]

@ ronmaltese...
Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.