Combine Dropdownlist with a search funktion

skykaiz

New Member
Joined
Sep 19, 2019
Messages
3
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?


ABC
(Search Box for entering company Code)Sales of Company A
Dropdownlist: Company AROI of Company A

<tbody>
</tbody>


Thanks!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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?

ABCDEFGHIJKL
1ACME$444,444Co. CodeCompanySalesROI
2ACME Widget Company2.3ACMEACME Widget Company$444,4442.3
3SGRSoo, Grabbit & Runn Lawyers$933,9391.1
4PH1Pickled Herring Ltd$55,5550.9
5PH2Paul & Harry Partners$240,6554.3
6RLXRubber Ladders Xtraordinary$98,0007.1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
skykaiz

Worksheet Formulas
CellFormula
B1=INDEX($K$2:$K16,MATCH($A$1,$I$2:$I$16,0))
A2=INDEX($J$2:$J$16,MATCH($A$1,$I$2:$I$16,0))
B2=INDEX($L$2:$L$16,MATCH($A$1,$I$2:$I$16,0))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
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?

ABCDEFGHIJKL
1ACME$444,444Co. CodeCompanySalesROI
2ACME Widget Company2.3ACMEACME Widget Company$444,4442.3
3SGRSoo, Grabbit & Runn Lawyers$933,9391.1
4PH1Pickled Herring Ltd$55,5550.9
5PH2Paul & Harry Partners$240,6554.3
6RLXRubber Ladders Xtraordinary$98,0007.1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
skykaiz

Worksheet Formulas
CellFormula
B1=INDEX($K$2:$K16,MATCH($A$1,$I$2:$I$16,0))
A2=INDEX($J$2:$J$16,MATCH($A$1,$I$2:$I$16,0))
B2=INDEX($L$2:$L$16,MATCH($A$1,$I$2:$I$16,0))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

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.
 
Upvote 0
Yes, that's what the INDEX does. You enter 3453 and it pulls up the company and values for that company code.

ABCDEFGHIJKL
13453$933,939Co. CodeCompanySalesROI
2Soo, Grabbit & Runn Lawyers1.1ACMEACME Widget Company$444,4442.3
33453Soo, Grabbit & Runn Lawyers$933,9391.1
4PH1Pickled Herring Ltd$55,5550.9
5PH2Paul & Harry Partners$240,6554.3
6RLXRubber Ladders Xtraordinary$98,0007.1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
skykaiz

Worksheet Formulas
CellFormula
B1=INDEX($K$2:$K16,MATCH($A$1,$I$2:$I$16,0))
A2=INDEX($J$2:$J$16,MATCH($A$1,$I$2:$I$16,0))
B2=INDEX($L$2:$L$16,MATCH($A$1,$I$2:$I$16,0))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top