Forum Discussion

NJ1000's avatar
NJ1000
Copper Contributor
Mar 24, 2020
Solved

Help in data validation formula: Cell cannot equal more than another

In my spreadsheet I need Cell A23 (which has it's value from cells A19:A22) to not be higher than the number already in cell H2 (which has it's value from cells B2:G2).   I am using data validation...
excel
Formulas and Functions
  • Charla74's avatar
    Charla74
    Mar 25, 2020

    NJ1000 

     

    Without going down the route of VBA and change events, you could do this with conditional formatting which will not give a message but can allow a message which is not usually visible to be displayed under certain conditions i.e. if cell H23 > H2.........see attached file (I added the formula in H23 adding A19:A22 to demonstrate - to see how it works, change cell A20 from 1000 to zero).

Charla74's avatar
Charla74
Iron Contributor
Mar 24, 2020

NJ1000

 

In my spreadsheet I need Cell A23 (which has it's value from cells A19:A22) to not be higher than the number already in cell H2 (which has it's value from cells B2:G2). 

 

Formula in cell A23 could be:

=IF(SUM(A19:A22)>H2,H2,SUM(A19:A22))

NJ1000's avatar
NJ1000
Copper Contributor
Mar 24, 2020

Hey Charla

 

Thanks for the suggestions. I gave it a shot, but it still allows A23 to be greater than H2.

  • Charla74's avatar
    Charla74
    Iron Contributor
    Mar 24, 2020

    NJ1000 

     

    Are you able to share the file here (or some sample data if sensitive)?

     

      • Charla74's avatar
        Charla74
        Iron Contributor
        Mar 24, 2020

        NJ1000 

         

        The formula seems to work, unless i'm missing some additional criteria you're looking for  -  See image...

         

        The formula simply checks whether the sum of A19:A22 is greater than the number in H2 - If it is then the result will be the value in H2, if not it takes the sum of A19:A22.

         

        Let me know if I have misinterpreted.

Resources