The value of cell (A1 for example) can only be “Y” or “N”
How can I force a second cell to be o (zero) if “N” is selected but allow any numeric input if “Y” is selected?
I an guessing it is conditional formatting but how do I set the second condition to allow input when needed?
I am sure it has been covered in other questions but I can’t find it.
Thanks in advance,
Gary.
Hello @GaryDown
Cell formatting, thus conditional formatting also, is used for view/display purpose only and does not apply any restrictions on possible cell value. For valid data range definition, you shall use Data Validity dialog, menu Data->Validity... item. But, as far as I know, it is not possible to apply different Data Validity rules for one cell, based on some condition. That means you can not define multiple Data Validity rules for one cell ang chose them based on predefined test, such as cell value, formula result and so on.
Fortunately, Data Validity feature allows to use formulas, that return area or array, so as a workaround for your goal, you can define such Validity rules combination:
Cell1 - Only Yes/No value accepted.
Cell2 - Only range of numbers accepted
Cell3 - IF formula, that returns 0, if Cell1="No", or returns Cell2 value, if Cell1="Yes"
Please also see this sample file attached.
Thanks SM_Riga,
I have found a solution based on on your idea of the 3rd cell. When either of the cell’s (B2:B3) content is changed a macro is called, via the 3rd cell (C2), to check the values.
Cheers,
Gary.
As a follow up, is it possible to access the name of the B3 cell from the input instead of passing it in in a parameter?
Thanks again.
Thanks,
I found using address was more suitable for my needs as I want to copy the cells without having to manually edit them.
