Paste text from lists

[Chris Webb]

Hi all,

I'm making a paint formulation spreadsheet. The user chooses ingredients and quantities, then specific weight, price etc are calculated. Its mostly working fine, using Vlookup. The problem comes in simplifying the way the user enters names of ingredients.

The ingredients are in two "tiers", i.e the group Solvents contains items Water, Texanol, White Spirit etc., the group Thickeners contains items SCMC,
HPMC, Acrysol etc., the group Extenders contains items China Clay, Talc,
Ropaque etc.
I'd like the user to be able to:
1) click on a command bar (or menu or whatever) -the Groups list appears..
2) click on a Group name -the items in that Group appear.
3) click on the chosen item -its name is pasted in.

I can do it with data validation, but only as one enormous list or using 2 columns.
I've failed to do it in custom lists and by creating a custom menu. I'm not smart enought to do it in visual basic without some pointers.

Can anyone help?

 

[Joe Was]

I dont understand each item in the formula should be in its own cell?

If that is the case then each type of item should have its own list?

You should then be able to use DropDown in-cell lists to select which item for each type of ingredient used in the formula?

You may want your mouse to do the typing for you?
If you have the same data, over and over again you may want to use dropdown list, to load the cell or cells for you?

Below are two types: a list of items that pulldown from within the cell that gets the data and be able to add your own item, that is not on the pulldown-dropdown list, then:

To the right of your "Formula" sheet build a list one item per row in one column. Click the column "ID-letter" to highlight the column, then use:

Insert-Name-Define then name your list.

Click the cell you want the list in, then:

From the Tool-Bar Menu:
Data-Validation-Settings (select "List" from Allow) in "Source" add =your list name, Like: =myList

Then copy the cell you just put the dropdown list in and highlight the other cells you want the same list in and hit special paste-validation.

Now when a cell with your dropdown is selected, it grows a dropdown arrow, which when clicked returns your selection list.

You just mouse select the item and it becomes the value or text in the cell!

To limit the selection to only the values in your list:

Data-validation-Settings
Allow: List
Check: Ignore blank
Check: In-Cell dropdown
Source: =$AA:$AA (this is the column "myList" is in.

Then:

Tab to Error Alert
Check: Show
Style: Stop
Title: Error!
Error Message: Only select from the dropdown list!

Now the user can put a wrong entery in the cell but they cannot move from the cell without an Error Box Message and the options to fix or erase their entery or start over.

Hope this helps? JSW