PDA

View Full Version : Solved: VBA lookup function inputbox



Sebbl99
10-04-2010, 02:39 AM
Hi Guys,

I am working on some data tables in Excel containing economical data

it looks like this

Columns

J K L
Rank Country in % of GDP

What I wanna do is, because the list is fairly long (from J3:L100), to set up a inputbox, that, when clicking it, asks you to enter the name of the country and then returns you the corresponding value of the country (which is in column L) by telling you what the value is and not by highlighting the cell where the value is located. Also, and this is the tricky part, I would like the input box to be so flexible that it is able to return me the top ten, or top five values in a list if I ask it to. Meaning, if I wanna see the top 5 countries with the lowest inflation rates, it returns me those along with their corresponding values. Or when I wanna see the top 5 countries with the highest inflation rates in the same list, it returns me these as well. Is there a VBA code flexible enough to accommodate those possibilities? I d be so grateful for some help as I am not much of a VBA man

Sebbl99
10-04-2010, 02:41 AM
oh the columns got messed up..so it should look like that

In column J-the ranks
in column K-the countries
in column L-the inflation rates

just examples..

mbarron
10-04-2010, 06:36 AM
Using a single input box would be difficult since you have more than one desired outcome.

I believ you can get the information you desire by using Filters. You can choose specific countries, top or bottom based on either a number (Top 5) or percent (bottom 15%).

If you want to go the macro route, I would suggest using a User form that allows you to pick your options.

Sebbl99
10-05-2010, 01:02 AM
thx so much for ur help. A filter was exactly what I needed!