HELP!!! Beginner here

axj

New Member
Joined
Nov 30, 2005
Messages
21
Ive got a slight problem, ive created lists with multi select options, ive linked the cells to a source with numbers. I want to know how to code the selection a user makes, so say a user selects various options from the list, how do i get this to calculate.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
I am kinda losing what you are trying to ask. I think you either want a Validation List or a Dependent Validation List.
 

axj

New Member
Joined
Nov 30, 2005
Messages
21
let me try and explain further. Ive made a list that has some research and development options, its a multiselect list, ive also linked the cells to the cost of each R & D option. How do i get a cell to display the total cost of all the options a user selects.

For the list i created i didnt do it directly in the vb editor, i drew it from the forms toolbar, does this clarify the q?
 

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
Let me see if I got a solution to your problem straight:

Assuming your research and development options are in column A and the cost in column B, as per my attached sheet, then I want to use a list box from Control Toolbox, not Forms, and C2 is the LinkedCell.
Book8
ABCD
1R&D
2East$100.00East
3North$200.00$ 100.00
4South$300.00
5West$400.00
6
Sheet1


Install the List Box from Control Toolbox, right and select Properties. Type (you need to type) in C2 as the LinkedCell.

Edit: The List Box isn't displayed in the picture above.

Right under that row type in the range of your development options as the ListFillRange. In my case it would be A2:A5.

Exit the Design Mode by clicking on the top left button (Design Mode) for that toolbox. If you later need to alter the range select Control Toolbox, right click, select Properties and enter Design Mode.

EDit: Select the range of research options and the cost (A2:B5 in my case) and click in the Name Box (next to the Formula Bar), (where you see C3 in the above example) and type in the name of that range. I called my range Research, as per the formula in C3.

The formula that returns the cost is in C3.

Hope This Helps

RAM
 

axj

New Member
Joined
Nov 30, 2005
Messages
21

ADVERTISEMENT

thank you, this helped, but i cant seem to get it to work with multiselect lists, how can i do this??
 

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
Please explain for me what you mean by multiselect lists and where do you get the data from?

RAM
 

axj

New Member
Joined
Nov 30, 2005
Messages
21

ADVERTISEMENT

Multiselect = when the user can select more than one item on a list and when the multiselect property has been changed to 2. so say if the user on the r&d selects more than one option how do i link it the figures to it, display the options in another cell and calculate them?
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Just a thought, but try playing around with checkboxes instead. When a Box is checked, it's linked cell will be TRUE. In the cell next to the linked cell for that item, enter the price.
Place all your linked cells in a column with prices to the right to form a table. Use SUMIF to get the total for the checked boxes. NOTE: The linked cells and table can be on another worksheet.

HTH

lenze
 

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
lenze,

You do have a good point there, but I'm turning to you for a question on my behalf. axj mentioned he was using a List Box from Forms. That list box has the option Multi as Selection Type under the Control tab in Format Control. That selection allows you to enter several options from the list, but then what? Does it take codes to go on from there?

RAM
 

axj

New Member
Joined
Nov 30, 2005
Messages
21
thanks that helped alot, i switched to check boxes and its working
 

Watch MrExcel Video

Forum statistics

Threads
1,119,123
Messages
5,576,228
Members
412,709
Latest member
AD04
Top