=INDEX('Sheet2'!A:A,CEILING(ROW()/5,1)+4) Function is returning repeats for some reason

[Sky188]

Good afternoon all,

I'm using the function =INDEX('Sheet2'!A:A,CEILING(ROW()/5,1)+4) to return a value from another sheet, and then copying that formula down with 3 blank cells between each formula. The formula works, but on occasion it will repeat itself.

To elaborate a bit, on Sheet1 I want B5, B9, B13, etc (Up to ~B10000) to have a formula that is equal to values from Sheet 2. I'd like B5 on Sheet1 to equal B2 on Sheet2, B9 on Sheet1 to equal B3 on Sheet 2, B13 on Sheet1 to equal B3 on Sheet2, and so on. While doing this, it works flawlessly until B61 on Sheet1, which is then repeated on B65 of Sheet1. It then works perfectly again until B141, which is then repeated on B145. It again works perfectly until B201, which is repeated on B205. Theres a pattern that each repetition has 15 cells between them (3 with value); that is, the second part of a repetition is 15 cells away from the first part of the next repetition.

I'm also using the function =INDEX(Sheet3!B:B,CEILING(ROW()/5,1)-455) to return a valeu to Sheet1 from Sheet3, and the above issue is even worse; the function still works, but the repetitions are much more constant, and happen either every other cell or every third cell (for example, B2412 isn't repeated, but B2416 is; 2424 isn't but both 2428 and 2436 are).

Ultimately, I might be able to find a way to simply hide the rows that have duplicates, but I'm hoping someone can shed some light as to why this is happening so I can further understand the =index + Ceiling function.

[FDibbins]

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Sky188]

I think I've come across the issue, but I'm not sure how to approach it. When I use the fill box (I think thats what the black square at the bottom of a highlighted cell is called) to copy down the function, it copies the selected cell first, rather than continuing onto the next.

So if A1 has a value of 1 and A1-A4 is selected and filled down, "1" will appear in both A1 and A5, and that pattern will repeat itself every 4 values.

[Ace_XL]

In A1 Start with

=INDEX(Sheet2!A:A,CEILING(ROW(1:100000)/4,1))

Copy down

[Sky188]

That works wonderfully, Thanks Ace_XL!