Link a cell in every second column of one worksheet, in a new worksheet.

[Ylona]

I have two worksheets.

I want to copy and paste/link something from one worksheet to another but, in worksheet A, the info I want is in every second column and in worksheet B, there is no columns in between.

Help - Excel beginner level.

 

[Toadstool]

Hi Ylona,

Here's my test data sheet A with row x column numbers as cell contents so it's easy to see where they originate.

Ylona.xlsx
	A	B	C	D	E	F	G	H	I
1	1 x 1	1 x 2	1 x 3	1 x 4	1 x 5	1 x 6	1 x 7	1 x 8
2	2 x 1	2 x 2	2 x 3	2 x 4	2 x 5	2 x 6	2 x 7	2 x 8
3	3 x 1	3 x 2	3 x 3	3 x 4	3 x 5	3 x 6	3 x 7	3 x 8
4	4 x 1	4 x 2	4 x 3	4 x 4	4 x 5	4 x 6	4 x 7	4 x 8
5	5 x 1	5 x 2	5 x 3	5 x 4	5 x 5	5 x 6	5 x 7	5 x 8
6	6 x 1	6 x 2	6 x 3	6 x 4	6 x 5	6 x 6	6 x 7	6 x 8
7	7 x 1	7 x 2	7 x 3	7 x 4	7 x 5	7 x 6	7 x 7	7 x 8
8	8 x 1	8 x 2	8 x 3	8 x 4	8 x 5	8 x 6	8 x 7	8 x 8
9	9 x 1	9 x 2	9 x 3	9 x 4	9 x 5	9 x 6	9 x 7	9 x 8
10	10 x 1	10 x 2	10 x 3	10 x 4	10 x 5	10 x 6	10 x 7	10 x 8
11
A

If you want links to column 2, 4, 6, etc then put this formula into cell A1 of worksheet B and copy across and down as many rows and columns as needed.

Ylona.xlsx
	A	B	C	D	E
1	1 x 2	1 x 4	1 x 6	1 x 8
2	2 x 2	2 x 4	2 x 6	2 x 8
3	3 x 2	3 x 4	3 x 6	3 x 8
4	4 x 2	4 x 4	4 x 6	4 x 8
5	5 x 2	5 x 4	5 x 6	5 x 8
6	6 x 2	6 x 4	6 x 6	6 x 8
7	7 x 2	7 x 4	7 x 6	7 x 8
8	8 x 2	8 x 4	8 x 6	8 x 8
9	9 x 2	9 x 4	9 x 6	9 x 8
10	10 x 2	10 x 4	10 x 6	10 x 8
11
B-AllEven

Cell Formulas
Range	Formula
A1:E11	A1	=IF(INDEX(A!$A$1:$ZZ$99999,ROW(),COLUMN()*2)="","",INDEX(A!$A$1:$ZZ$99999,ROW(),COLUMN()*2))

If you want column 1, 3, 5 etc then use this:

Ylona.xlsx
	A	B	C	D	E
1	1 x 1	1 x 3	1 x 5	1 x 7
2	2 x 1	2 x 3	2 x 5	2 x 7
3	3 x 1	3 x 3	3 x 5	3 x 7
4	4 x 1	4 x 3	4 x 5	4 x 7
5	5 x 1	5 x 3	5 x 5	5 x 7
6	6 x 1	6 x 3	6 x 5	6 x 7
7	7 x 1	7 x 3	7 x 5	7 x 7
8	8 x 1	8 x 3	8 x 5	8 x 7
9	9 x 1	9 x 3	9 x 5	9 x 7
10	10 x 1	10 x 3	10 x 5	10 x 7
11
B-AllOdd

Cell Formulas
Range	Formula
A1:E11	A1	=IF(INDEX(A!$A$1:$ZZ$99999,ROW(),COLUMN()*2-1)="","",INDEX(A!$A$1:$ZZ$99999,ROW(),COLUMN()*2-1))