Drop down

**jfkennedy** · 10-10-2011, 03:28 PM

I am new to excel functions/programming.

I need your expertise help.

I have a table with two columns

one is Numbers and other is Type

in the numbers there are numbers 1 to 10

and in the type column i have a,b,c,d e, etc.,,

I am looking as how i can pull the list of Numbers with reference to a

it should display all the numbers with type a
if i select type c it should display all numbers with b and hide other numbers.

thank you for your help in advance.

The reason I am looking for this is if i select D as type and it should display the values associated with it so that I can cut and paste it to use it in my script(VB),

John

**Apelcius** · 10-10-2011, 04:32 PM

what is the over all concept here, that you are trying to accomplish? it's a little hard to visualize your purpose. are you attempting to list a set of numbers in one sheet, then select a type, and have the main sheet list those numbers?

here's an example of what i think you are asking:
list of cars, numbered (like a list of VINs), with the associated colors. when i want to select all blue cars, then i use a drop down under "color of vehicle", and select blue. and i want it to hide all non-blue cars, and only show blue cars. is this correct?

if so, which part are you having questions about? actually inserting a drop down menu, and giving it only a certain list of possible answers? making it hide all non-preferred items? or, all the above?

be happy to help once i get a good understanding of what your goal is

**jfkennedy** · 10-10-2011, 05:10 PM

Thank you Apelcius thats what I am looking for your example is perfect and a correct one.

here is my scenario.

I am planning tohave a excel spreadsheet with Patches and Type

where Patches have Numbers in the column
Type has XP/Office 2003/2007/2010/IE6/IE7/IE8 Patches numbers.

XP is standard

XP/Office 2003/IE6
XP/Office2007/IE8
XP/Office2010/IE6
XP/Office2010/IE8

I would like to get the patch numbers based on the above scenarios.

Thank you once again

John

**Apelcius** · 10-10-2011, 06:36 PM

ok, so two steps here.
1) drop down menu:
to create a drop down menu, select the cell you wish to make it. Then, "Data" --> "Validation". This opens up a control window. In the "Allows" menu, you can choose "list". In "Source:" menu, type your desired items:
for the car example: type "Blue, Green, Black, Red". the section is comma delimited, meaning commas separate each possible selection.
2)this is the more time consuming method: macros.
since you wish to actually affect the settings of rows/columns, you'll have to use macros. for this, i will have to experiment a little, and get back to you, to find the easiest solution.
unless of course another helps out before i can get a solution together.

but, the drop down menu portion is fairly straight forward, once you know where to find it...
(will get back here with possible macros as soon as i can-unless someone beats me to it)

**jfkennedy** · 10-10-2011, 08:06 PM

Thank you Apelcius. I am also attaching the .xls file for ready reference. I have created a format in excel which has all values. But I am looking at displaying on XP/IE6/.Net/Office Comp in one scenario by going to dropdown box and uncheck the headings i dont need. In this scenario i had to uncheck IE7,IE8,Office2007,Office2010 so that I can only the XP/IE6/.Net/Office Comp on spreadsheet.

If i need only XP, i should uncheck everything but for XP so that it displays on XP values.

Thank you once again for your time and appreciate your help.
John

**jfkennedy** · 10-11-2011, 08:15 AM

apelcius

i got the spreadsheet and this is how i have done it.

1st column has all the headings
2nd, 3rd, 4th etc., columns has numbers

highlighed the first column and selected filter which gives me the drop down box with check boxes option.

in this i can unselect the stuff i dont need and click ok which will display the numbers accordingly.

Thank you once again for your time and appreciate it.

if you have any other better solution. pls let me know

John