Excel application.

[ritbc]

I have a lagre table in Excel, containing Part Number, Description, Model Name.

One part number can exist for more than one model.

I wish to make a simple application in Excel, where the user will type the part number in a CELL of an excel worksheet, and the Model names will appear in nearby CELLs.

can anybody tell me how to do it?


Example is here:


Part No.........Desription.....................................Qty.......Model

000120375 -NUT HEX G6.35 X1.27X5.74X8.8             -2- 2205LSGM

000120384 -WASHER SPRING LOCK 1/2 ZP                -2- 2205LSGM

000120393 -WASHER PLAIN G8.712X17.450X1.295         -1- 2185GM

000120393 -WASHER PLAIN G8.712X17.450X1.295         -1- 2205LSGM

000120393 -WASHER PLAIN G8.712X17.450X1.295         -1- 3385GM

000120393 -WASHER PLAIN G8.712X17.450X1.295         -2- 4335GM


if the user types "000120393", corresponding models 2185GM, 2205LSGM, 3385GM, 4335GM should appear.

 

[Faseeh]

Hi ritbc,


Welcome to the forums!!!


Assuming your data is present in Cell A2:D8 Incl. Header Rows, enter these formula any where and drag down, your required Part No. is present in E2:


For Part No:

=IFERROR(INDEX($A$3:$A$8,SMALL(IF($A$3:$A$8=$E$2,ROW($A$3:$A$8)),ROW(A1))-2,0),"…")


For Description:

[code]=IFERROR(INDEX($B$3:$B$8,SMALL(IF($A$3:$A$8=$E$2,ROW($A$3:$A$8)),ROW(B1))-2,0),"…")


For Qtty:

=IFERROR(INDEX($C$3:$C$8,SMALL(IF($A$3:$A$8=$E$2,ROW($A$3:$A$8)),ROW(B1))-2,0),"…")

For Model:

=IFERROR(INDEX($D$3:$D$8,SMALL(IF($A$3:$A$8=$E$2,ROW($A$3:$A$8)),ROW(B1))-2,0),"…&#34[/code]


Press Ctrl+Shift+Enter for each and drag down, similar formula just first argument of Index() is changed.


Regards,

Faseeh

 

[kaushik03]

Hi ritbc,


Firstly welcome to this forum.


Now regarding your query, I assume your data is arranged as follows(from A1 to B9, where A1 and B1 is your headngs):


PartNo. Model

120393 2185GM

120394 5268MT

120394 3452SSSGT

120393 4335GM

120394 2689SG

120394 2658TN

120393 3385GM

120393 2205LSGM


Say at D2, you have the part number as 120393

Then at E2, write the following formula:


=IFERROR(INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$D$2,ROW($A$2:$A$9)-MIN(ROW($A$2:$A$9))+1,""),ROW(A2))),"") ....press ctrl+shift+enter to enter the formula as an array formula(don' only press enter)


Than copy it down as needed.


change the value at D2(other part number i.e. 120394) and test the formula


Hope this helps...


Regards,

Kaushik

 

[kaushik03]

Oops...I just overstepped Faeesh...


Sorry Faseeh!!!

 

[Faseeh]

No problem kaushik03, you are welcome!! :D