Ask Your Question

numeric input depending on other cell value

asked 2019-01-18 10:06:39 +0100

GaryDown gravatar image

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.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-01-19 05:30:51 +0100

GaryDown gravatar image

updated 2019-01-21 03:41:42 +0100

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.


edit flag offensive delete link more

answered 2019-01-18 17:04:37 +0100

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

Cell2 - Only range of numbers accepted rule_2

Cell3 - IF formula, that returns 0, if Cell1="No", or returns Cell2 value, if Cell1="Yes" rule_3

Please also see this sample file attached.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-01-18 10:06:39 +0100

Seen: 42 times

Last updated: Jan 21