Oct 09 2020 07:19 AM
Oct 09 2020 07:19 AM
I've set up a data entry sheet with 2 dependent drop down lists (using indirect data validation). Users are only allowed to select and then type in number only in the next cell. Users can select an item multiple times as well. I want to somehow limit users from exceeding in "sum" of a particular selection.
For example, user can select USA and New Jersey 10 times in random order and type in any number each time. but the said number can not go above 50. How do i do that?
The problem is that it can not be cell location generated but selection generated as I don't know on which row the user will select a certain item. Looking forward for a positive response. Thanks
Oct 09 2020 07:50 AM
Think there are many ways in Excel to do this.
One is conditional formatting. If the value is exceeded, then the red cell (as an example). So the users could also have a learning defect and then correct this until it is no longer red (that's just a thought).
Use conditional formatting to highlight information
The other possibility would be with VBA code
Private Sub Worksheet_Change (ByVal Target As Excel.Range) If Range ("H10:H20")> 0 Then MsgBox ("Value exceeded") Exit Sub End If End Sub 'Here is a small approach that just needs to be adjusted.
You could also do it with a formula ... as already said there are many ways that lead to a solution in Excel ... this would just have to suit the user.
*It would also be helpful if you inform us about the Excel version and your operating system, so that you can always receive faster and more precise help.
I would be happy to know if I could help.
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
Oct 09 2020 09:51 AM
I'm using Office 2016 on Mac OS.
Appreciate your reply, but I don't think it solves my problem. I want specifically to restrict people from going above the limit. As an example, I'm attaching a simple data set. So if you look at it, B and New York is selected in row 2 and 10. If they are selected, i don't want the total of these 2 to exceed a certain amount. Also, i don't know when they will be selected. It could be on row 1000 as well.
Oct 09 2020 10:14 AM