Add List When Cell Populated?

[charllie]

Hi Folks,

I have a problem and not sure if this can be done without using code.

Using data validation i have created a drop down list in cell A1 of sheet1. Depending what is choosen from the drop down list in A1 i want to then add other text to sheet 2 starting in cell b2.

Difficult to explain so will do it in bullet point form below:

• Select "Roast" from A1 Sheet1
• If "Roast" is selected then i want to add
• Potatoe in B2 sheet2
• Peas in B3 sheet2
• Carrots in B3 sheet2
• If "Brekkie" is selected in A2 Sheet1 then i want to add
• Bacon in next empty cell in coloumn B sheet2

I hope the above makes sense to help me get some help in getting started.

Thanks all

Charllie

 

[Jerry Sullivan]

Hi Charllie,

It sounds like you want to do this with formulas instead of VBA code. If that's the case, I think it depends on the details of the functionality you want to achieve.

For the Roast-Potatoe-Peas-Carrots part of your example, you could have a simple lookup table and have formulas in Sheet2, Column B cells lookup values of Columns 1,2,3,4 of your table...based on the Validation List item selected.

The "Brekkie" example is more daunting without code. Do you want to add on to the list that you started with Potatoe-Peas-Carrots? I believe that would take some tricks, and would need to work within some very limiting constraints.

One way to visualize the limitations are that formulas without code are somewhat static... they need to pull the values to their cells, whereas code can push values or formulas into cells (which is much more desirable when building a list).

I'd be glad to help with some code if you want to go that route.

 

[charllie]

Hi JS411,

Thanks for your reply, i appreciate your time and help.

Ok, will try and explain a little better as what i am trying to achieve.

I have a menu planner that i am using to plan daily meals on a weekly basis. But for this example i will concentrate on sheet 1 which is week 1 (i have sepearte sheets for each week). I also have other sheets which contain the list used for the drop down boxes etc.

I use the drop down boxes to select my meals: for example,if selecting dinner, i could choose and roast beef from one drop down box and then i have 3 side order drop down boxes from which i would choose the vegetables etc. All this works fine and i will add at this point, i did not create it but simply downloaded. I am trying to adapt it to suit me.

I now want to create a new spreadsheet on A4 size and name it as my shopping list. So what i then want to do is when i select roast beef it will add
roast beef joint to the list. As i choose each side order it will add the item to the shopping list.

Once my weeks meal p[lanner has been completed i should then have a shopping list to print out.

Ideally i would like to place the items in sections such as mains, veg and frozen etc but this is not a must.

I hope that clarifies things and would really appreciate any help.

Thanks

Charllie

 

[Jerry Sullivan]

Charllie,

That description helps me understand what you are trying to do. Since you have multiple dropboxes, this might be challenging to resolve through an exchange of posts.

If you can post your workbook to a hosting site or exchange through a PM, I'll be happy to recommend some code or formulas which we can post on this thread.