Drop list with macro and some data

[cw1628]

Hi,

I am having some difficulties in making this template for my boss so can someone please give me a hand on this?

This is the situation:

I need to create a drop list and when I select the drop list, it will give me a few selections for example: Sports, News, Music. (This I can do). On the right side of the cell, i will have week 1, week 2 all the way to 25.

Once I select Sports, the data from the Sports section will appear in the next few cells such as basketball, baseball, tennis and so on along with the data in week 1, 2, ... etc There will be an empty cell right below the row of the drop list cell that will allow me to input a new type of sport as well as add data to the week 1, 2, columns.

Once it has been added, this new sport will be saved under the Sports section.

I hope this is clear enough!

[wmorrison49]

Can you post the workbook? Partly because I don't know how to make a macro launch via a drop down list. Can you explain this to me? Sorry, I have just never done that before. I will try to help you with your problem too, haha.

[wmorrison49]

I responded in the New Users forum as you posted it in two different places

[cw1628]

Okok. This is just an example given the fact that I cannot provide you with the real data (work related).

The weeks on the right hand side allow the user to input the number of hours for each category. For example: Select sports from the list --> Will make a new row to allow me to insert a new sport (say Rugby) --> Then I can input the number of hours I have played for Rugby. Rugby will then be saved under the sports section along with other sports.

Hope this helps.

[wmorrison49]

The JPEGs didn't come out very well, I can't view them clearly enough to see them. How do you get the macros to launch from selecting an option from the drop down menu?

[cw1628]

Oh sorry. I haven't! That is why I'm asking. I have only done the basics which is doing the simple drop down list (using the validation function and the data from the 2nd worksheet). I'm hoping someone can figure out the macro part that will pull down the rest of the sports section (example) once I click on sports and as well as allow me to add a new line to input a new sport.

[Dean England]

You may be able to do what you want by taking a look at this site on Dependent Dropdown Lists


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

I think this may be what you want.

Dean

[cw1628]

hmm. I've seen this before but I'm not sure that I can apply it or not. I will give it a 2nd try.

[VBA Noob]

See if this link helps

http://www.excelforum.com/showthread.php?t=605311

VBA Noob

[cw1628]

Hmm I get where you're coming from but it's not quite what I'm looking for.
Lets use the Validation example that you supported. If I were to choose Football in the drop down list from cell H1. Then Premiership and C'ship would have appearred in cell H3, and H4. Cell H2 would be a blank row that will allow me to input a new item associated with Football.

So what I need to do right now is once Football is selected, premiership and c'ship would automatically show up on those 2 cells.

Does that clear things up?

[royUK]

Can you post an example workbook, your jpegs are too blurred to see what you are aiming for.

[cw1628]

Once again, I haven't done an awful lot. This is just the template of what I would like it to be.

So once sports is selected. All the sports in that category will be listed at the bottom (where I have put comments in the file) along with the data in the number of weeks (bare in mind that I have not made a table in the other sheet). So for example. when the list of sports have appeared. Basketball will have 5 in week 1 and 3 in week 2..
While baseball will hve 4 in week 1 and 0 in week 2.
This is not real and is not shown in my workbook but i'm just showing you what kind of end product I am looking for

[royUK]

I can only give you guidance without some example to work with. I would make the selection from the Data validation list, which would in turn populate the other cells using VLOOKUP. So you would need a table on another sheet for the VLOOKUPS to work from.

[cw1628]

I will give that a try but if you can provide just a simple example it would be greatly appreciated.

[cw1628]

I dont think I ever uploaded the example file. I thought I did. Would you be able to show me what you mean with the Vlookup? Thanks

[VBA Noob]

See if this helps

VBA Noob

[cw1628]

GREAT!! It works! Thanks so much.
Looking at the example file again, I want to be able to add a new sport into the "example" worksheet and not in to the "data" worksheet. I want to add soccer into the sports list without going to teh data worksheet to add. So when I switch to the music selection and switch back to the sports selection, it would show soccer in the sports list. Does that make sense?

[cw1628]

hmm anyone out there that knows what to do?

[cw1628]

Hi,

I have a drop list that I've created and this is what I want it to do.

A drop list on C3 with options such as Sports, News, Music. If Sports is chosen from the drop list, then starting on D4, it will show all the sub categories from the sports option (such as basketball, baseball, tennis etc).

If I choose the 2nd option News, then all the sub categories from the News option will show on cell D.... (wherever the sports sub categories end).

I also have a table on the next worksheet where I gave it a name to make the drop list, as well as the names of each of the subcategories. Now what I am also looking for is, if I want to insert a new sub category for one of the options it will show up in my other worksheet so when I click on that option, that new subcategory will show on the sheet. I think a macro will be needed. Can anyone teach me how to do this? thanks

[Excelenator]

Can you post your file to the site? It may help in getting you the answer you seek.