Formula to link a value from a vertical list to a horizontal row

[bearcub]

I have the following situation where I have employee ID's in a list that I want to link into a separate row.

The destination source (in this example) is in Row 2

The list starts in row 10.

I want to copy (by formula) each item in the list starting

in row 10 to every other column in Row 2 (destinations are highlighted in yellow).

Is there a formula that will to move down the list by 1 row when I move across 2 columns? I don't think I can use Transpose for this, correct?

thank you for your help.

Michael

 

[Toadstool]

Hi Michael,

Will this do it for you?

Bearcub.xlsx
	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
2	AA8		BB3		CC5		ZZ5		XX3
3
4
5
6
7
8
9	List
10	AA8
11	BB3
12	CC5
13	ZZ5
14	XX3
15
16
Sheet1

Cell Formulas
Range	Formula
R2:AC2	R2	=IF(INDEX($R$10:$R$9999,ROUNDUP(COLUMNS($R$2:R2)/2,0))="","",IF(ISEVEN(COLUMN()),INDEX($R$10:$R$9999,ROUNDUP(COLUMNS($R$2:R2)/2,0)),""))

 

[BobUmlas]

Without a complicated formula:
Enter R10 in cell R2 (Without the "=", just R10).
Select R2 and S2. Use the fill handle to fill right. You will see R3 in T2, R4 in U2, R5 in V2, etc.
Select R2:the end, and replace R with =R and you're done!

 

[Peter_SSs]

A simpler formula option?

21 01 08.xlsm
	R	S	T	U	V	W	X	Y	Z	AA
1	Employee ID	Split Amount (%)	Employee ID	Split Amount (%)	Employee ID	Split Amount (%)	Employee ID	Split Amount (%)	Employee ID	Split Amount (%)
2	AA8	100	BB3	100	CC5	100	ZZ5	100	XX3
3
8
9	List
10	AA8
11	BB3
12	CC5
13	ZZ5
14	XX3
Sheet2 (2)

Cell Formulas
Range	Formula
R2,Z2,X2,V2,T2	R2	=INDEX($R10:$R14,COUNTIF($R1:R1,"E*"))

 

[Dossfm0q]

Book1
	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1
2	EXUU076SL		7EOAE5J4G		QHTXMCEEP		DAIW153F8		BJFHQK81L		8YL53U867		03XQOlKJ5		067L4KHTL		EZBJR5ZKH		9Z7ROMTGG	XL5GK8PF7		XZS54J5XW		0
3
4
5
6
7
8
9	ID	Link To
10	EXUU076SL	R2
11	7EOAE5J4G	T2
12	QHTXMCEEP	V2
13	DAIW153F8	X2
14	BJFHQK81L	Z2
15	8YL53U867	AB2
16	03XQOlKJ5	AD2
17	067L4KHTL	AF2
18	EZBJR5ZKH	AH2
19	9Z7ROMTGG	AJ2
20	XL5GK8PF7	AL2
21	XZS54J5XW	AN2
Sheet3

Cell Formulas
Range	Formula
R2,AO2,AM2,AJ2:AK2,AH2,AF2,AD2,AB2,Z2,X2,V2,T2	R2	=INDIRECT(ADDRESS(CEILING(COLUMNS($R$1:R1)-1+(ROW($R$10)*2),2)/2,COLUMN($R$10)))

After copy Formula into First Cell you want, Select it with next blank right One then copy From bottom Right edge to right side

 

[Peter_SSs]

After copy Formula into First Cell you want, Select it with next blank right One then copy From bottom Right edge to right side

I note that you did not follow this procedure with the first image in your post but in relation to this suggestion, did you look at the sample data in post 1? The cell to the right is not blank.
Even if it was then selecting the two cells with the much simpler formula I posted and dragging across would produce the required results without adding the complexity of that formula or the unnecessary ongoing calculation burden of a volatile function like INDIRECT.

 

[Dossfm0q]

Greeting Peter
this is only for fast copy to the target cells and to keep empty cell for need data and avoid duplicate Job (copy and then delete)

and Thank you for sharing your experience and knowledge to improve member performance.

 

[Peter_SSs]

this is only for fast copy to the target cells and to keep empty cell

My point was that there is no empty cell.