Forum Discussion
Set the value of a cell within a range and change it automatically
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)
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)
Thank you Matt, but that's not what I'm looking for.
What you are doing is not letting the value of the editable cell to exceed the value of another cell, but what I want is, if I have in cell H1 the value "4" and I change the value of the cell B1 to 2, just automatically the value of the cell H1 would change to 2.
As I see there is nothing in excel I can do this with, I will mark your response as the best one for trying to help :)
You can just use a short code snippet to accomplish your end goal (see attached .xlsm file):
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$1" Then Range("H1") = Target.Value End If End Sub