How do I get non-consecutive cell numbers dragged from the same column to the same row?

[wardjdim]

Hi all

I am asking a question I asked as an additional question in a previous thread

If I have a column full of numbers and I just want to add several numbers from this column in another row (so from vertical order to put them horizontally. Let's say, the column I pick for the numbers is column A and the numbers go from cells A1 to A20. I want to pick a number every 5 rows (So, numbers on columns A1, A6, A11 and A16)

How do I make these four numbers appearing on rows B1, C1, D1 and E1 automatically. So, this means not by writing at B1 =A1, at C1, =A6. etc.

In other words, I need the cell number to jump by 5. To read the data 5 cells downwards automatically (meaning, always 5 cells downwards)

Cheers again

 

[Jonmo1]

Try

=INDEX($A:$A,((COLUMNS($A1:A1)-1)*5)+1)

The 5 is the interval (every 5 rows)
The 1 is the column to begin in (1=A,2=B,etc)
Do not change the COLUMNS($A1:A1)-1, it has nothing to do with location of your data.

Hope that helps.

 

[wardjdim]

Cheers for that

However, when I am dragging cell B1 to C1, the result is #REF

I am not sure why. The only thing that is changing is COLUMNS($A1:A1) and becomes COLUMNS($A1:B1)

 

[Jonmo1]

Are you sure you copied and pasted my posted formula EXACTLY as I posted it?

 

[wardjdim]

Are you sure you copied and pasted my posted formula EXACTLY as I posted it?

Not exactly

Because the situation I am into is not the same as the example

However all the cells affected are correctly put and on the first cell, I am getting the requested number

 

[Jonmo1]

It works for me

Excel Workbook
	A	B	C	D
1	a	a	f	k
2	b
3	c
4	d
5	e
6	f
7	g
8	h
9	i
10	j
11	k
Sheet1

 

[Jonmo1]

Perhaps you should describe your exact situation, not a simplified example of it..

 

[Jonmo1]

Perhaps this confused you..

I misspoke.

this
The 1 is the column to begin in (1=A,2=B,etc)
should read
The 1 is the ROW to begin in

 

[wardjdim]

Ok, maybe you re right. But, still I don't know what I am doing wrong

I am sorry to tire you from that aspect

I have two different columns (B and E) in one sheet. Each column has 18 Elements. The Elements of column B are standard names repeated every 20 cells (the 18+2 BLANK CELLS) and the Elements of column E are numbers

Each number of column E represents a value for the respective element on column B that is on the same row (e.g. B310 has element XXXXX and value 15 at cell E310)

The 18 elements of column B start counting from B310 and last until B327. Cells B328 and B329 have no elements (similarly, E328 and E329 have no value as well). Then they start again at B330 and stop at B347 etc.

They last until B987 and E987 respectively

I have copied and pasted the 18 elements of column B in Sheet2's column A2-A19. Then, horizontally, I took columns B-AI on Sheet2 to use them as the place to paste the values of column E. For each Column B value, we should normally get the result of the 1st column+20 rows horizontally. So, for Sheet2, CellB2, your value is correct.
=INDEX(Sheet1!$E310:Sheet1!$E310,((COLUMNS(Sheet1!$E310:Sheet1!E310)-1)*20)+1)

But if I drag it, it doesn't work

I don't know why

I hope I explained it well enough

 

[Jonmo1]

OK,

1. I clearly stated
Do not change the COLUMNS($A1:A1)-1, it has nothing to do with location of your data

2. The first part
Sheet1!$E310:Sheet1!$E310
This is only a single cell reference.
It needs to be a reference to the ENTIRE dataset in column E, something like
Sheet1!$E$310:Sheet1!$E$5000

310 is the FIRST row of the data in column E, 5000 is the last row of data in column E

So try

=INDEX(Sheet1!$E$310:$E$5000,((COLUMNS($A1:A1)-1)*20)+1)