YES/NO with values assigned

[dsrussell]

Sorry that my first post should be so obvious, but I've tried searching the forum and can't find exactly what I'm looking for. Here goes:

I'm working in Excel 2007 and have a cell with a defined name, that I use elsewhere in a formula. All I want to be able to do is to have a YES/NO drop down in the same cell with YES = 1 and NO = 0, so that if the user selects YES, then the variable name is given the value 1 and 0 for NO.

I'm OK in setting up a drop down list using ranges etc, but I've seen in another spreadsheet somewhere that you can place a cute little bit of "code" in the Source field of the Data Validation window, so that the user sees YES and NO in the drop down, but in reality is selecting 1 or 0 (that they don't see). From memory it uses an INDEX command is something along the lines of INDEX({"YES";"NO"},1,0). However, this doesn't work - not least, as the arguments are probably inconsistent.

I'm aware I could get round this using a lookup function, and I've seen a clever way using an Eval function on this forum (which doesn't work for my later needs). Does anybody have any idea of how to do this in the way that I've seen before?

With many thanks!
Darren

 

[GlennUK]

What about having a defined name that calculates a value of 1 or 0 from the cell contents ( YES/NO ). Define a new name, menu command Insert/Name/Define ( or ribbon Formulas/Defined Names/Define Name ), with a reference of a formula of:

=MATCH($B$4,{"NO","YES"},0)-1

and use that elsewhere in formulas.

 

[AlisaA]

Rather than set up your Name as just referencing the cell, what about using a formula like this one?

=IF(Sheet2!$K$7="Yes",1,0)

(assuming Sheet2!$K$7 has your Yes/No validation box in it)

Does this help?

 

[AlisaA]

Glenn, you're quicker on the draw! I like yours, too!

 

[GlennUK]

Thanks AlisaA.

 

[Scott Huish]

Shouldn't need a function at all:

=(Sheet2!$K$7="Yes")+0

 

[GlennUK]

Hotpepper, quite right!

But mine can be extended for longer lists than 2 options if required.

 

[Scott Huish]

Mine can as well, but it will need an OR.

 

[scottylad2]

Excel Workbook
	E	F
14	0	no
Sheet1

Excel Workbook
	E	F
14	1	yes
Sheet1