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

[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.

 

[Norman Jones]

Hi Marika,

You might look at the built-in functionality of AutoFilter an
AdvancedFilter.

If you are not familiar with these features, see Debra Dalgleish's tutorials
at:

http://www.contextures.com/xlautofilter01.html

and

http://www.contextures.com/xladvfilter01.html

 

[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!!

 

[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

 

[tina]

Hi
I had another thought could you use a pivot table with states as group
Tina

 

[marika1981]

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