Is there a DRAG option to fill every second cell?

[sherpamick]

I have an excel sheet which has a series of numbers one cell after the other. I must transfer them to a form where there is 1 space between the cells. See attached sample. If all the cells in the second FORM TAB were one after the other I could simple enter in the first cell in FORM TAB "=Adjust!B3" and drag the cell to the bottom but because it is every other cell I cannot do this.
Note, I don't think VLOOKUP or MATCH & INDEX will work as there are small differences in the way the rooms are worded, also I have Hundreds of cells to copy.

Thanking you in Advance.
Sherpamick

[shg]

The obvious suggestion is to remove the blank rows between cells.

If you get rid of the spurious spaces in your data and make the inputs on worksheet Adjust consistent,

	A	B	C	D
3	Ground Floor	Room 17A	45	C3: =IFERROR(VLOOKUP(CONCATENATE(A3, " - ", B3), Adjust!$A$3:$B$11, 2, FALSE), "")
4
5	Ground Floor	Room 21M	52
6
7	First Floor	Hall A	21
8
9	West Wing	Room 95	22
10
11	West Wing	Room 7	16
12
13	West Wing	Hall B	85
14
15	West Wing	Hall K	45
16
17	First Floor	Hall B	78
18
19	First Floor	Room 23	74

[sherpamick]

SHG, Thanks - Can't do that as I would later have to reinstate them, Document "Adjust" can not be amended. IE it must be returned with the Empty spacing Cells. Also Concatenate will not work as we added "hints" to the names for our own direction. Appreciate your time and help.

[shg]

	A	B	C	D
3	Ground Floor	Room 17A	45	C3: =IF(MOD(ROWS($C$3:C3), 2), INDEX(Adjust!$B$3:$B$11, (ROWS($C$3:C3) + 1)/2), "")
4
5	Ground Floor	Room 21M	52
6
7	First Floor	Hall A	21
8
9	West Wing	Room 95	22
10
11	West Wing	Room 7	16
12
13	West Wing	Hall B	85
14
15	West Wing	Hall K	45
16
17	First Floor	Hall B	78
18
19	First Floor	Room 23	74

But that's a really terrible design.

[shg]

Or enter the formula shown in C3, select C3 and empty cell D3, and drag down:

	A	B	C	D
3	Ground Floor	Room 17A	45	C3: =INDEX(Adjust!$B$3:$B$11, (ROWS($C$3:C3) + 1)/2)
4
5	Ground Floor	Room 21M	52
6
7	First Floor	Hall A	21
8
9	West Wing	Room 95	22
10
11	West Wing	Room 7	16
12
13	West Wing	Hall B	85
14
15	West Wing	Hall K	45
16
17	First Floor	Hall B	78
18
19	First Floor	Room 23	74

[sherpamick]

Thanks shg, ill give it a go.

[shg]

You're welcome.