Drop down to show only renaming count...%

[kuldeepjainesl]

Hello All,

This is similar to my previous question on data validation. Currently i am using the custom formula =(SUM($F$7:$F$21)<=100) in data validation to keep the Sum total below 100.

i am trying to achieve this by provide only available numbers in drop down

Like data validation is used for A2:A25. in A2 user has full 100% to be used but if user has filled 50% in A2 then in A3 only remaining 50% should be available and if user fill the 25% in A3 then only 25% is available inA4

 

[Debraj]

Hi kuldeep!

Do you want something like this..

Check DV for details..

 

[kuldeepjainesl]

Hello Debraj, No this trick is known to me and currently implemented in my Excel. I want to Provide the Drop down list to user with only available.
In this you are checking the Sum value and giving message once Sum is exceeding but i want that in second drop down to list only the numbers remaining . For example if user selected 45 in Cell A2 from drop down then A3 onward the max value availabe in dropdown will be = 100- 45 (value in A2 Cell). Hope this Clear the situation

 

[kuldeepjainesl]

Hello All, Any other idea to achieve this.

 

[kuldeepjainesl]

Hello Deb and Others, Please let me know if any further information is needed to understand the problem.

 

[kuldeepjainesl]

i could sort out this. i used the dynamic range to create the remaining available numbers.

=Sheet1!$D$1:INDEX(Sheet1!$D:$D,(COUNTA(Sheet1!$D:$D)-SUM(Sheet1!$F$3,Sheet1!$F$4)))

Solved

 

[Debraj]

Hi Kuldeep..

thought its looks interesting.. but I am unable to applied it on my scenario..

can you please share the file.. with applied DV & named range..
If someone came here to look for solution.. then it will helps a lots..

 

[kuldeepjainesl]

Please find the Excel. You can check. The Correct Dynamic range used is as

=Sheet1!$A$1:INDEX(Sheet1!$A:$A,(COUNTA(Sheet1!$A:$A)-Sheet1!$B$16))

and put the DVLIST in Data validation.