Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT: Creating a drop-down menu to perform v-lookup function
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT: Creating a drop-down menu to perform v-lookup function
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 --- Regards, Norman "marika1981" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT: Creating a drop-down menu to perform v-lookup function
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!! "Norman Jones" wrote: 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 --- Regards, Norman "marika1981" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT: Creating a drop-down menu to perform v-lookup function
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 "marika1981" wrote: 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!! "Norman Jones" wrote: 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 --- Regards, Norman "marika1981" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT: Creating a drop-down menu to perform v-lookup function
Hi
I had another thought could you use a pivot table with states as group Tina "marika1981" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT: Creating a drop-down menu to perform v-lookup function
Thanks Tina - I'll give your two ideas a try...fingers crossed! Much
appreciated!! "tina" wrote: Hi I had another thought could you use a pivot table with states as group Tina "marika1981" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating a graph from a drop down menu | Excel Discussion (Misc queries) | |||
Need urgent help creating a nested if/lookup problem or other solution | Excel Discussion (Misc queries) | |||
Creating a drop-down menu | Excel Programming | |||
Creating a drop-down menu | Excel Programming | |||
Creating a drop-down menu | Excel Programming |