Forum Discussion

Peter Evans's avatar
Peter Evans
Copper Contributor
Sep 04, 2018

Conditional Data Validation

I have a spreadsheet and on it cells G7 to G14 and G20 to G24 can be either Blank or 1.  I need to restrict to total count to 1 in these cells so users can only choose 1 option, and it is a Quantity not a Boolean.

 

Is this possible without using VB?  This is for an order form and it will be sent via email so it would be better without VB.

 

Thanks a lot 

excel
Formulas and Functions

1 Reply

Sort By
  • Philip West's avatar
    Philip West
    Steel Contributor
    Sep 04, 2018

    Hia,

    I've attached what I think you are looking for. Which is that people should only be allowed to select 1 option from the 4? So if they select "1" then the other 3 options shouldn't be available any more..

     

    What I have done is create 4 named ranges (option1, option2...) and made those the target of the data validation list. Then in each of those 4 cells I've added an IF that checks if one of the other 3 cells has been picked. If it has then the 1 is removed.

     

    The location of the named ranges doesn't really matter, you can hide those on another sheet, or hidden rows or something.

    Hope that helps.

Resources