Excel 2007 : Drop down menu conditioning

[Greennunu]

Hi, I'll start off by saying I am very new to excel program. I have been doing most of my excel work by google searching but I am not very familiar with terms and names so I ran into a bit of a problem.

I want to set up a drop down menu for each month of the year, when I select the appropriate month the 10 products being sold by a company will appear on the next colum for that month.

Further more, I am not sure if this is entirely possible, but once I have selected that month from the drop down menu, if I were to change the values in the corresponding product to have that value be updated and saved for the future.

[JBeaucaire]

Show us what you mean.

Click GO ADVANCED and use the paperclip icon to post up a desensitized example workbook.

[Greennunu]

Sorry for the late reply but here is an example.

[JBeaucaire]

1) To pick a month and have all the values from that month appear in your table can be done with a formula. Put this in G3 and copy down:

=INDIRECT($F$2&"!B" & ROW(A1))

2) If you TYPE in any of those cells, the formula is deleted, so that wouldn't be good initially.

The only way to SEND info from the Overview sheet back to the monthly would require VBA. Do you want to invoke macros in your project? Not everyone does.

If you go the VBA route, then you wouldn't need the formula above either, the same macro that SENDS info to the other sheets could PULL the data in to display it, too.

If you decide you're OK with VBA, then you need to finish designing your sheet first. If I wrote a macro based on the Overview sheet as you posted, would that really be exactly where everything is going to end up?

[Marcol]

1/. There are many ways to hide your lookup list for months.

You could type the list separated with commas directly into the validation dialogue.
This cannot change in your case, because the formula depends entirely on the sheet names matching the validation list (or vice versa)

If you want to change sheet names once your workbook is set up you might need to use vba to update everything automatically.

The most common way to "Hide" your lookup list is to put it on a separate sheet and name the range. Then refer to that in your validation.

2/. This in G3

Please Login or Register  to view this content.

Drag/Fill Down
will read the value from each sheet into your table based on the month selected.

3/. You can't read into a cell and then export an amended value.
The cell either has a formula or it doesn't.

You will need another column for amendments and will probably need a VBa solution if you want to do this, and read from the sheet at the same time.

I can't really see the point of doing any of this. What am I missing?

[EDIT]
I went for a more complicated formula than JB to allow for sorting of the individual sheets, and missing sheets.
Didn't see Jerrys' post until I posted.

[Greennunu]

Thanks for the help so far, I will look more into VBA and come back here later. Can you point out a good source to learn more about VBA and Excel in general?