Limit sum of entry in multiple dependent drop down list

%3CLINGO-SUB%20id%3D%22lingo-sub-1764340%22%20slang%3D%22en-US%22%3ELimit%20sum%20of%20entry%20in%20multiple%20dependent%20drop%20down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1764340%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20set%20up%20a%20data%20entry%20sheet%20with%202%20dependent%20drop%20down%20lists%20(using%20indirect%20data%20validation).%20Users%20are%20only%20allowed%20to%20select%20and%20then%20type%20in%20number%20only%20in%20the%20next%20cell.%20Users%20can%20select%20an%20item%20multiple%20times%20as%20well.%20I%20want%20to%20somehow%20limit%20users%20from%20exceeding%20in%20%22sum%22%20of%20a%20particular%20selection.%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20user%20can%20select%20USA%20and%20New%20Jersey%2010%20times%20in%20random%20order%20and%20type%20in%20any%20number%20each%20time.%20but%20the%20said%20number%20can%20not%20go%20above%2050.%20How%20do%20i%20do%20that%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%20it%20can%20not%20be%20cell%20location%20generated%20but%20selection%20generated%20as%20I%20don't%20know%20on%20which%20row%20the%20user%20will%20select%20a%20certain%20item.%20Looking%20forward%20for%20a%20positive%20response.%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1764340%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1764532%22%20slang%3D%22de-DE%22%3ESubject%3A%20Limit%20sum%20of%20entry%20in%20multiple%20dependent%20drop%20down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1764532%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F826465%22%20target%3D%22_blank%22%3E%40OAN1234%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3ET%3CFONT%20size%3D%223%22%3Ehink%20there%20are%20many%20ways%20in%20Excel%20to%20do%20this.%3C%2FFONT%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CFONT%20size%3D%223%22%3E%3CSPAN%20class%3D%22%22%3EOne%20is%20conditional%20formatting.%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3EIf%20the%20value%20is%20exceeded%2C%20then%20the%20red%20cell%20(as%20an%20example).%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3ESo%20the%20users%20could%20have%20a%20learning%20defect%20and%20then%20correct%20this%20until%20it%20is%20no%20longer%20red%20(that's%20just%20a%20thought).%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%3EUse%20conditional%20formatting%20to%20highlight%20information%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%223%22%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fuse-conditional-formatting-to-highlight-information-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f%3Focmsassetid%3Dha102809768%26amp%3Bcorrelationid%3Dbe9d841f-bd1b-462f-a679-d3c9b165244f%26amp%3Bui%3Den-us%26amp%3Brs%3Den-us%26amp%3Bad%3Dus%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fuse-conditional-formatting-to-highlight-information-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f%3Focmsassetid%3Dha102809768%26amp%3Bcorrelationid%3Dbe9d841f-bd1b-462f-a679-d3c9b165244f%26amp%3Bui%3Den-us%26amp%3Brs%3Den-us%26amp%3Bad%3Dus%3C%2FA%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EThe%20other%20possibility%20would%20be%20with%20VBA%20code%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change%20(ByVal%20Target%20As%20Excel.Range)%0AIf%20Range%20(%22H10%3AH20%22)%26gt%3B%200%20Then%0AMsgBox%20(%22Value%20exceeded%22)%0AExit%20Sub%0AEnd%20If%0AEnd%20Sub%0A%0A'Here%20is%20a%20small%20approach%20that%20just%20needs%20to%20be%20adjusted.%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3ESo%20you%20could%20do%20it%20with%20a%20formula%20...%20as%20already%20said%20there%20are%20many%20ways%20that%20lead%20to%20a%20solution%20in%20Excel%20...%20This%20would%20just%20have%20to%20suit%20the%20user.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%20%3CSPAN%20class%3D%22%22%3E*It%20would%20also%20be%20helpful%20if%20you%20inform%20us%20about%20the%20Excel%20version%20and%20your%20operating%20system%2C%20so%20that%20you%20can%20always%20receive%20faster%20and%20more%20precise%20help.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1765226%22%20slang%3D%22en-US%22%3EBetreff%3A%20Limit%20sum%20of%20entry%20in%20multiple%20dependent%20drop%20down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1765226%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%20Office%202016%20on%20Mac%20OS.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20your%20reply%2C%20but%20I%20don't%20think%20it%20solves%20my%20problem.%20I%20want%20specifically%20to%20restrict%20people%20from%20going%20above%20the%20limit.%20As%20an%20example%2C%20I'm%20attaching%20a%20simple%20data%20set.%20So%20if%20you%20look%20at%20it%2C%20B%20and%20New%20York%20is%20selected%20in%20row%202%20and%2010.%20If%20they%20are%20selected%2C%20i%20don't%20want%20the%20total%20of%20these%202%20to%20exceed%20a%20certain%20amount.%20Also%2C%20i%20don't%20know%20when%20they%20will%20be%20selected.%20It%20could%20be%20on%20row%201000%20as%20well.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1765292%22%20slang%3D%22de-DE%22%3ESubject%3A%20Limit%20sum%20of%20entry%20in%20multiple%20dependent%20drop%20down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1765292%22%20slang%3D%22de-DE%22%3EUnfortunately%2C%20I%20can't%20help%20you%20with%20Excel%20on%20Mac%20OS.%3CBR%20%2F%3EBut%20think%20that%20some%20experts%20who%20are%20familiar%20with%20Excel%20on%20Mac%20OS%20will%20be%20in%20touch%20when%20they%20read%20your%20message.%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20your%20feedback%2C%20your%20understanding%20and%20your%20patience.%3CBR%20%2F%3E%3CBR%20%2F%3ENikolino%20%3CBR%20%2F%3E%20I%20know%20I%20know%20nothing%20(Socrates)%3C%2FLINGO-BODY%3E
New Contributor

Hello, 

 

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

3 Replies

@OAN1234 

 

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

https://support.microsoft.com/en-us/office/use-conditional-formatting-to-highlight-information-fed60...

 

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.

 

Nikolino

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.

 

@Nikolino 

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. 

 

Unfortunately, I can't help you with Excel on Mac OS.
But think that some experts who are familiar with Excel on Mac OS will be in touch when they read your message.

Thank you for your feedback, your understanding and your patience.

Nikolino
I know I know nothing (Socrates)