Dragging formulas

[Tango T]

Hi All

I'm having a problem I've not seen in excel before (Office 365) when dragging formulas

An example of the problem is something like this. I have a spreadsheet with say 2 sheets

In sheet1 column A I have data, every 5th line is a name of something
In sheet2 column A I have a formulas such as

=Sheet1!A1
=Sheet1!A5
=Sheet1!A9
=Sheet1!A13

My issue is that when I drag this down the column it's not incrementing as expected (or has in the past) it does do the increments in 5, it's just the first new dragged into jumps back a few numbers....i.e it looks like


=Sheet1!A1
=Sheet1!A5
=Sheet1!A9
=Sheet1!A13
=Sheet1!A6
=Sheet1!A11
=Sheet1!A15
=Sheet1!A19

Any help would be appreciated as it's driving me crazy

[shg]

You could enter the first two values as a1 and a5, then drag down:

	A
2	a1
3	a5
4	a9
5	a13
6	a17
7	a21
8	a25
9	a29

Then replace a with =sheet1!a

[Tango T]

Thanks for the quick reply and this does work as a work around

Do you know why this is happening, is it a known bug with Excel?

[shg]

I've never seen Excel behave as you describe.

[Tony Valko]

You could use this formula:

=INDEX(Sheet1!A:A,ROWS(A$1:A1)*4-3)

Copy down as needed.

[soom]

Not sure if my post will help you but I'm guessing you're highlighting cells on Sheet2 Column A1:A4 then dragging them down to whatever row you need, right?

I'm sure you've noticed but it's going:

A
B
C
D
A+4
B+4
C+4
D+4

Where 4 is the amount of cells you highlighted to drag down. Shg probably has the most reasonable answer to this but could a this website link work for your purposes? https://support.microsoft.com/en-us/kb/152265

I have a feeling that Excel is purposely designed to prevent you from increasing based on the formulaic differences (in this context 5) versus continuing to repeat. The drag formula I believe was designed to be only used with simplistic numerical operations (N,N+X,N+X2,N+X3, etc) where + can be replaced with any + / - / * / % operator.


*Also* the disparity between what you wrote is a bit puzzling too.. You say every 5th line has a name and the corner cell of Excel is A1. If A1 has no name and A5 does. Wouldn't you be counting from A1, 2, 3, 4, A5? You somehow go from a 5 (A1 to A6) then continue on with increments of 4. This might be due to the way your spacing / number counting in your head on sheet 1 is contributing to the issue possibly.