Combine Dropdownlist with a search funktion

[skykaiz]

Hello guys,

I've created a dropdownlist with company names. With each selection, I got a different calculation for key figures like sales, turnover ratio, Roi and so on.

But now, each company got a unique company code (5 digit number). So my question, Is it possible to create a search box where you can enter the company code and the dropdownlist jumps to the company name?


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD](Search Box for entering company Code)[/TD]
[TD]Sales of Company A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dropdownlist: Company A[/TD]
[TD]ROI of Company A[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks!

 

[Toadstool]

I think I'm missing something as, assuming the company code is unique, then why can't you just retrieve the company name without the dropdown and the other data directly?

	A	B	C	D	E	F	G	H	I	J	K	L
ACME	Co. Code	Company	Sales
ACME Widget Company	ACME	ACME Widget Company
SGR	Soo, Grabbit & Runn Lawyers
PH1	Pickled Herring Ltd
PH2	Paul & Harry Partners
RLX	Rubber Ladders Xtraordinary

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]$444,444[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]ROI[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2.3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$444,444[/TD]
[TD="align: center"]2.3[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$933,939[/TD]
[TD="align: center"]1.1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$55,555[/TD]
[TD="align: center"]0.9[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$240,655[/TD]
[TD="align: center"]4.3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$98,000[/TD]
[TD="align: center"]7.1[/TD]

</tbody>

skykaiz​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=INDEX($K$2:$K16,MATCH($A$1,$I$2:$I$16,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=INDEX($J$2:$J$16,MATCH($A$1,$I$2:$I$16,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=INDEX($L$2:$L$16,MATCH($A$1,$I$2:$I$16,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[skykaiz]

I think I'm missing something as, assuming the company code is unique, then why can't you just retrieve the company name without the dropdown and the other data directly?

	A	B	C	D	E	F	G	H	I	J	K	L
ACME	Co. Code	Company	Sales
ACME Widget Company	ACME	ACME Widget Company
SGR	Soo, Grabbit & Runn Lawyers
PH1	Pickled Herring Ltd
PH2	Paul & Harry Partners
RLX	Rubber Ladders Xtraordinary

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]$444,444[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]ROI[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2.3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$444,444[/TD]
[TD="align: center"]2.3[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$933,939[/TD]
[TD="align: center"]1.1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$55,555[/TD]
[TD="align: center"]0.9[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$240,655[/TD]
[TD="align: center"]4.3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$98,000[/TD]
[TD="align: center"]7.1[/TD]

</tbody>

skykaiz​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=INDEX($K$2:$K16,MATCH($A$1,$I$2:$I$16,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=INDEX($J$2:$J$16,MATCH($A$1,$I$2:$I$16,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=INDEX($L$2:$L$16,MATCH($A$1,$I$2:$I$16,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

The whole thing is like a situational calculator for companies key figures. For example if I choose Company X in the Dropdownlist, it calculated key figures like RoI, RoE, Turnover etc. for Company X The data are on other worksheets and as I choose the Company Excel does the calculating.

But because there are many companies, I would like to insert a search funktion, where you can search the Companies in the dropdownlist by its company code. If for example I enter 3453, it shows automatically the calculations for Company X, if I enter 3454, it jumps to Company Y in the dropdownlist and so on.

 

[Toadstool]

Yes, that's what the INDEX does. You enter 3453 and it pulls up the company and values for that company code.

	A	B	C	D	E	F	G	H	I	J	K	L
Co. Code	Company	Sales
Soo, Grabbit & Runn Lawyers	ACME	ACME Widget Company
Soo, Grabbit & Runn Lawyers
PH1	Pickled Herring Ltd
PH2	Paul & Harry Partners
RLX	Rubber Ladders Xtraordinary

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]3453[/TD]
[TD="align: right"]$933,939[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]ROI[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1.1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$444,444[/TD]
[TD="align: center"]2.3[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3453[/TD]

[TD="align: right"]$933,939[/TD]
[TD="align: center"]1.1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$55,555[/TD]
[TD="align: center"]0.9[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$240,655[/TD]
[TD="align: center"]4.3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]$98,000[/TD]
[TD="align: center"]7.1[/TD]

</tbody>

skykaiz​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=INDEX($K$2:$K16,MATCH($A$1,$I$2:$I$16,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=INDEX($J$2:$J$16,MATCH($A$1,$I$2:$I$16,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=INDEX($L$2:$L$16,MATCH($A$1,$I$2:$I$16,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Swayzy]

Use a Powerpivot to handle your data then on the actual pivot table you can use a slicer.


It is though unclear wether you want a box that you want to in real time fliter available companies as you type, much as Google auto completes what you are typing in their search engine. Or if you just want to have list were you select an item (code) and results are updated for that specific item