Forumla Doesn't Auto-Increment Correctly

[coltonyyz]

Hi!

My problem is...

I have this formula in A1:

=INDEX('Sheet1'!$L107,'Sheet2'!$L107,'Sheet3'!$L107)

Then I have this in A2:

=INDEX('Sheet1'!$L138,'Sheet2'!$L138,'Sheet3'!$L138)

I want to be able to drag down the formula where it automatically fills in the next correct cell which happens to be exactly 31 cells apart.

So the next formula in A3 should be:

=INDEX('Sheet1'!$L169,'Sheet2'!$L169,'Sheet3'!$L169)

But when I drag the formula down, it doesn't keep the correct tabulated formula. It generates the wrong one.

How can I get it to automatically go down 31 cells when dragging down the formula?

[TMS]

Are you dragging down or across? Confused by A1 and B1 and down.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[coltonyyz]

Actually, I forgot how I setup this formula when I did my spreadsheet months ago. I don't need to drag down (to answer your question; thank you!). Right now I have the #REF error but when I fill in data to the corresponding Sheet1, Sheet2, it auto-populates the correct data! I forget how I did it but it's working correctly!

BUT, if ever I needed to drag down, how would I force excel to go down 31 cells and capture the data in that corresponding cell? Otherwise, how can I delete this question since it's no long a problem for me?

Note: I edited the main question for clarity. Thanks again for pointing out my mistake!

[TMS]

Something like this, copied down:

Please Login or Register  to view this content.

[TMS]

Not entirely sure what this is meant to do:

Please Login or Register  to view this content.