Paste text from lists

Chris Webb

New Member
Joined
Oct 25, 2002
Messages
1
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?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,865
Messages
5,598,530
Members
414,245
Latest member
Major Aly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top