macros in excel 2000 (need help!)

… don’t know what you guys will do…
but i have a huge favour to ask…

my supervisor needs to revise a spreadsheet, but the person that it was assigned to hasn’t done squat and it’s due tomorrow. so i was asked to somehow take care of it.
it was given to me this morning.
can you guys help me out?
I have been looking on the net for guides, text… help of any kind to just get this simple macro… but it’s hard to find tutorials…

…so any help would be appreciated…
here is what needs to be done…
basically i need a macro that gives a menu of departments to choose from. once one department is selected, the appropriate menu of items would be listed in another menu in the spreadsheet.

thanks so much!.. i will be doing more research…
thanks again

I’m using Excel 97 still, but give this a go:

Type your list in a column, using one cell per item.

Select the cell where you want your dropdown list to appear.

Select Data/Validation from the menu.

On the Settings Tab, set Allow to “List”.

This should bring up another box on the same tab named Source. Type in the cell range of your list from the first step or use the build button (looks like an itty-bitty spreadsheet with a red arrow on it in the right side of the Source box) and select the range with your mouse. The range should look something like this when you’re done: =$A$1:$A$4. A of course is the column your list is in and the 1 and 4 represent the first and last rows of your item list. Replace as necessary for your specific needs.

Click OK. The cell should now have a combo dropdown arrow next to it. Click it to see if it functions like you want it to. If it’s okay, copy that cell to all the other cells you need this capability on.

Hmm, maybe I should mention that if everything works out, you can subsequently hide the column (make it a column way out to the right somewhere) so that no one can monkey with it. It’s also a good idea to sort the item list alphabetically (highlight list, click Data/Sort menu); it makes it much easier for your users to scan the list and get to the one they want.

Urrgh! I didn’t read past the “gives a menu of departments to choose from” part. Okay, it gets more complicated from here, but you still don’t need to do any coding. See if you can follow me.

Let’s use this table as an example:



Finance          |  Item 1  |  Item 2   |  Item 4   |
Human Resources  |  Item 46 |  Item 5   |  Item 2   |
Warehouse        |  Item 13 |  Item 32  |  Item 29  |
Purchasing       |  Item 6  |  Item 11  |  Item 13  |

Let’s say “Finance” is in cell A10.

First, set up the cell as I stated in my first post for the department dropdown. Let’s call that cell A1 (Source for this list is $G$1:$J$1).

Under the last row of your first Item column (this would be cell B14 in our example), enter this formula: = LOOKUP($A$1,$A$10:$A$13,B10:B13). Make sure you get the $'s in the right spots so when you copy this formula only the last argument will change in relation.

Copy B14 to C14 and D14.

Now click on cell B1 to make your second dropdown list. Do the same as you did for A1 except make the Source: =$B$14:$D:14.

Presto! You’ve made a dynamic dropdown list based on a selection made from another menu.

Hope this helps.

Urrgh! I didn’t read past the “gives a menu of departments to choose from” part. Okay, it gets more complicated from here, but you still don’t need to do any coding. See if you can follow me.

Let’s use this table as an example:



Finance          |  Item 1  |  Item 2   |  Item 4   |
Human Resources  |  Item 46 |  Item 5   |  Item 2   |
Warehouse        |  Item 13 |  Item 32  |  Item 29  |
Purchasing       |  Item 6  |  Item 11  |  Item 13  |

Let’s say “Finance” is in cell A10.

First, set up the cell as I stated in my first post for the department dropdown. Let’s call that cell A1 (Source for this list is $A$10:$A$13).

Under the last row of your first Item column (this would be cell B14 in our example), enter this formula: =LOOKUP($A$1,$A$10:$A$13,B10:B13). Make sure you get the $'s in the right spots so when you copy this formula only the last argument will change in relation.

Copy B14 to C14 and D14.

Now click on cell B1 to make your second dropdown list. Do the same as you did for A1 except make the Source: =$B$14:$D:$14.

Presto! You’ve made a dynamic dropdown list based on a selection made from another dropdown list.

Hope this helps.

Gasp! Ignore the 9:58 AM post. I corrected a few cell references. Go with the one immediately above this post.

Gads, will I ever remember to include all the information in one post?

In your lookup table the first column needs to be sorted alphabetically for the lookup functions to work properly. Select your entire table of departments and items (or whatever), choose Data/Sort, make sure the first column (Column A in the example)is selected for Ascending, and click OK.

you are amazing!

i was reading through some filter data stuff, pivot charts… all these things and I couldn’t get it to work.

i came back to this post, thinking no one would help.

but i went through your post and…
it worked perfectly!!!

thank you!

i showed my supervisor the smarts of the straightdope members.
:smiley:

Great! Hope I saved you some heartache. Tell your supervisor that he/she only has to name their firstborn Horseflesh now. And give you the rest of the day off!