Forum Discussion
Solved
Set the value of a cell within a range and change it automatically
Hello to everyone!
I have seen that you can set a data validation for a cell, so if you want the value of that cell to be within a range, an error message would appear.
What I want is to set a value of a cell within a range defined for another two cells, so if the value of any of those two cells changes, the value of the first cell would change too if necessary, without any error message.
I know it could be done with a formula, but I need to change the value of the cell manually too.
Thank you so much for the help!
Maybe something like this dynamic dropdown will work. It will list out all of the numbers between 1 and the Maximum that you specify. See attached .xlsx file for an example.
It uses this data validation formula: =OFFSET($E$1,0,0,B1,1)
7 Replies
Greetings!
As understood, please find .xlsx.
Thanks,
I believe what you're referring to is called dependent drop downs. Debra Dalgleish has a great article on how you can create this type of validation. Please see the below link which has a step by step tutorial, video and example file:
https://www.contextures.com/xlDataVal13.html#dependent
Thank you for your answer Matt, but that is not what I am looking for.
I can't explain it properly as I am not an english person, but I'm gonna try it with an example:
The thing is: I have a cell with a formula and its current value is 10 and another cell whose value is editable and currently is 7. If the value of the cell with the formula changes to 5, I would like the cell whose the value is 7 automatically change to 5, as it cannot exceed the value of the cell with the formula, but it can be lower than it (I could manually change it to 3, for example).
I hope you can understand it this way and if you could tell me wether if it is possible or not.
Thank you!
Maybe something like this dynamic dropdown will work. It will list out all of the numbers between 1 and the Maximum that you specify. See attached .xlsx file for an example.
It uses this data validation formula: =OFFSET($E$1,0,0,B1,1)
