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
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