Excel calculate cell based on listbox selection 2

[jazminecat]

Hi guys -

So I have an excel spreadsheet with a simple yes/no listbox. I want another cell to make a calculation based on that selection. If the user selects Yes, for example, I want cell D7 to say "Employee", if the user selects No, I want it to say "Continue"

Can anyone help me with this? Thanks!

 

[DaleWatson123321]

A formula like the following is all you require...

=CHOOSE(A1,"Employee","Continue")

Of course replace A1 with the Cell link of your ListBox.

Regards, Dale Watson

 

[firefytr]

What kind of listbox is it? How did you create it? If it's created from the Forms toolbar, you can just link the cell (right click | Format Control... | Control (tab) | Cell Link, specify cell to return (ensure Single selection type)) and it will return the item number (i.e. 1, 2) of whichever selection is made. This means you must know which is first, the Yes or the No.

Now if it's made from the Controls Toolbox, it's an ActiveX control. This means if you (being in Design mode) right click the object and select Properties, set the LinkedCell property to the cell you want the value returned to. The great thing about using an ActiveX control is that it will return the actual value, not the list number of that item. The bad thing is that it will not work on a Mac and is generally more difficult to troubleshoot/maintain than Forms controls.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[jazminecat]

Hm, well maybe the problem is I made the list using data/validation.

Here's the layout:

B2 has a list made using Data-->validation, and selecting List, and typing in "yes, no" in the settings. This created a dropdown where the user can select yes or no.

Cell D7, needs to say "Employee" if the user selects yes, and "Continue" if the user selects no.

Currently, this yields a #Value! error:

=CHOOSE(B2,"Employee","Continue")

The big picture is that this is a form, and there are three of these yes/no questions, and a field much further down makes a calculation based on what's in the Employee or Continue field.

Any ideas? Thanks for your help.

 

[firefytr]

First of all, your data validation should be ..

Yes,No

.. instead of ..

Yes, No

See the difference? Both will have a length of 3 as there is a space in front of the "No" (" No").

You could use ...

=IF(B2="Yes","Employee","Continue")

This is based on the value either being "Yes" or not (should otherwise be a "No", although it's not implicitly stated).

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[jazminecat]

Hey Firefytr - thanks! that did the trick. have a star!

 

[firefytr]

Why thank you.

Take care!

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci