Prevent number increase; allow number decrease

faeryluv

New Member
Joined
Sep 1, 2018
Messages
35
Is it possible to lock a cell down to where the numbers entered can't be increased, only decreased? When sending out our spreadsheets for employees to adjust orders, for example, sometimes we can't allow them to increase the amount. We want to allow them to decrease, but not increase. Half of them ignore our instructions, increase the numbers, and make us have to go through dozens of lines to move the amounts back which takes a long time. Just thought I'd check with all of you geniuses out there with the hopes of making my life a lot easier! Lol! Thanks in advance <3
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,035
Office Version
  1. 2016
Platform
  1. Windows
Hi,

You can use data validation.
Go to data validation > Select whole number from Allow list
And there you can set the Min and MAX limit

Regards,

Humayun
 

faeryluv

New Member
Joined
Sep 1, 2018
Messages
35
Hi,

You can use data validation.
Go to data validation > Select whole number from Allow list
And there you can set the Min and MAX limit

Regards,

Humayun

This is fantastic, thank you! I don't suppose there's a way to do this without having to add a second column? I'd hate to have to enter the original amount into each cell validation, that is why I have added a separate column. See attached screenshot.
 

Attachments

  • Data Validation.png
    Data Validation.png
    11.3 KB · Views: 4

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,035
Office Version
  1. 2016
Platform
  1. Windows
So does that sort out the problem ??
 

faeryluv

New Member
Joined
Sep 1, 2018
Messages
35

ADVERTISEMENT

So does that sort out the problem ??
By adding an additional column, yes. However, it would be nice to not have to do that. There's always hundreds of different item amounts so I can't type in a Maximum amount for each cell. Therefore, I have added an extra column (Original Amount) to set Maximum amount as "=A3". Do you think this is my best option for this, or is there a better way?

1607018817051.png
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,035
Office Version
  1. 2016
Platform
  1. Windows
Do you think this is my best option for this, or is there a better way?
Sorry.. I don't think of any better option
Experts out here may come up with a better solution

Regards,

Humayun
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You can put a formula into the cells, to pull the original value across & use the data validation to prevent the value from being increased.
+Fluff v2.xlsm
CD
2189189
3134134
4131131
57070
6189189
76969
8178178
95656
105757
11114114
Main
Cell Formulas
RangeFormula
D2:D11D2=C2
Cells with Data Validation
CellAllowCriteria
D2:D11Whole number<=C2


But there is nothing to stop a user from changing the original value in col C
 

faeryluv

New Member
Joined
Sep 1, 2018
Messages
35
You can put a formula into the cells, to pull the original value across & use the data validation to prevent the value from being increased.

But there is nothing to stop a user from changing the original value in col C

Thank you! Humayun's solution works great; I was just trying to keep from having to add an additional column as the spreadsheet already contains many columns. However, adding the extra column is much better than nothing. I appreciate your assistance.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,643
Messages
5,626,059
Members
416,159
Latest member
CheeseBurger5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top