Automatic data filling by cell /Column reference

[anujteetwal]

Dear All,

i have to prepare a machine loading pattern sheet where child parts of an assemblies to be filled in machine at designated place.

In my excel sheet machine designated location are L1/1 - L1/8 to L3/8.
Child part location is fixed in database are mentioned from L4 :L14 along with there designated name.

i want to fill fixed location automatically when i search Assembly name in A3. Eg ; INPUT SHAFT ASSEMBLY, also my unfilled cell become different color or else.

How can i do this- pls help

Capture.JPG

Machine Loading Pattern.xlsx

[AliGW]

In B5 copied across:

=IFNA(INDEX($K$4:$K$14,MATCH(B4,$L$4:$L$14,0)),"")

In B10 copied across:

=IFNA(INDEX($K$4:$K$14,MATCH(B9,$L$4:$L$14,0)),"")

In B15 copied across:

=IFNA(INDEX($K$4:$K$14,MATCH(B14,$L$4:$L$14,0)),"")

CF rule:

=B5<>""

Applies to:

=$B$3:$I$3,$B$8:$I$8,$B$13:$I$13

[anujteetwal]

ohh. thanks a lot. its solved now.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you are no longer using Excel 2010, please update your forum profile.

[anujteetwal]

one thing is still missing. its not changing the data as per A3 cell where different assemblies number will come. i have added dropdown list now.
pls check now Attachment 655860

[AliGW]

Your chosen layout is not optimum. Ideally you need a different layout. Also, you have not included any locations in the second set of data - please do this and post the workbook again.

[anujteetwal]

done.
pls check again Attachment 655861

[AliGW]

Lay out the lookup table this way:

Excel 2016 (Windows) 32 bit

	K	L	M	N
3		INPUT SHAFT ASSEMBLY	Assembly Location	Remark
4	INPUT SHAFT ASSEMBLY	INPUT SHAFT	L3/3	SAFA
5	INPUT SHAFT ASSEMBLY	Ball Bearing 62x25x17	L3/1	BVD
6	INPUT SHAFT ASSEMBLY	Ball Bearing 20x47x14	L2/3	FBN
7	INPUT SHAFT ASSEMBLY	Helical Spring	L1/1	FH
8	INPUT SHAFT ASSEMBLY	SPRING PLATE	L1/2	SDFG
9	INPUT SHAFT ASSEMBLY	Washer	L1/3	THDRF
10	INPUT SHAFT ASSEMBLY	Wave Washer	L1/5	HGFE
11	INPUT SHAFT ASSEMBLY	Shim 20x32x3.100	L1/7	DHERT
12	INPUT SHAFT ASSEMBLY	PINION GEAR G11	L3/7	JR
13	INPUT SHAFT ASSEMBLY	DAMPER-LINK	L3/8	RGWE
14	INPUT SHAFT ASSEMBLY	Circlip 28x2	L2/8	WG
15		COUNTER SHAFT ASSEMBLY	Assembly Location	Remark
16	COUNTER SHAFT ASSEMBLY	COUNTER SHAFT	L1/3
17	COUNTER SHAFT ASSEMBLY	FIX-GEAR G21	L1/5
18	COUNTER SHAFT ASSEMBLY	Ball Bearing 20x47x14	L1/7
19	COUNTER SHAFT ASSEMBLY	Shim 20x32x3.100	L3/7

Sheet: Example

Then this in B5:

=IFNA(LOOKUP(2,1/(($K$4:$K$50=$A$3)*($M$4:$M$50=B4)),$L$4:$L$50),"")

B10:

=IFNA(LOOKUP(2,1/(($K$4:$K$50=$A$3)*($M$4:$M$50=B9)),$L$4:$L$50),"")

B15:

=IFNA(LOOKUP(2,1/(($K$4:$K$50=$A$3)*($M$4:$M$50=B14)),$L$4:$L$50),"")

[anujteetwal]

thank you very much.

[AliGW]

My pleasure.