URGENT: Creating a drop-down menu to perform v-lookup function

M

marika1981

Help!! I'm trying to create a drop-down menu as part of a slightly complex
v-lookup task. And I desperately need some expertise!

I have a table with about 350 rows and 8 columns. The names of the US
states appear in the first column - some often, some only once - in random
order. On a neighboring worksheet, I need to create a drop down menu
offering the names of all the states. When one state is selected, all the
rows starting with that state need to appear in a matrix, array or table of
some sort directly below the drop-down menu (presumably using v-lookup).

I know how to use v-lookup, but I'm struggling to figure out how to:

1) create a drop-down menu macro
2) link the v-lookup formula to the item selectd in the drop-own menu
3) if possible, preserve (or "lock") the v-lookup output area so that
end-users don't screw up the v-lookup formulation accidentally

Any ideas?? Or alternative suggestions on how to get the same done another
way???

THANK YOU!!! Very, very much appreciate any help.
 
M

marika1981

Norman,

Thanks for your input. Yes, I do know about the Filter function.
Unfortunately, our client wants the search to be performed (alond with
several others) all on a worksheet of their own. If you have any other
ideas, albeit far more complex, I'd be grateful!!!

Thanks again!!
 
T

tina

Hi
To do list I would in a blank cell do data validation validation criteria
allow list source column with states

then write following macro where mydata is your 8 columns and 350 rows
mylist is cell with validation list this filters to list criteria then
copies visible cells only to sheet2 or whether you want

this can be run by selecting macro manually after list selection or could
use worksheet_change function I am not expert on this but if look at other
question about this should find help you need

Sub Macro1()

Range("MYDATA").Select
Range("MYDATA").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("MYLIST"), Unique:=False
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone,
SkipBlanks:=False _
, Transpose:=False
Sheets("Sheet1").Select
Application.CutCopyMode = False
ActiveSheet.ShowAllData
End Sub

hope this helps
Tina
 
M

marika1981

Thanks Tina - I'll give your two ideas a try...fingers crossed! Much
appreciated!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top