Question

[ntrddragn]

Hi Im new here but i have a question regarding excel but i dont know what the best function to use so i cant search it. =/

i have a sheet where users can pick from a list (list is from data validation). each item they pick has a value. what i want is for them to stop being able to pick from list once the max value (40) is reach. once that is reach and if they wanted to add more they will need to change one or of the items they have already picked. this will decrease/increase the value until again it reaches 40.

I was able to use data validation to stop at the max but the issue is once they reach the max they cant change the items that was picked already.

thanks
richard

 

[Sal Paradise]

You could create dynamic lists.

Have your lists which have their values and whatnot. Have a lookup to tally the score from each field in a separate cell. Use INDIRECT() and names to make the lists dynamic based on how many points they have left. If they have no points left, you just leave the list blank (so they can't pick anything).

See this article:
http://www.contextures.com/xldataval02.html

 

[Tigs81]

Hi ntrddragn

I would use the custom validation field to do this
Column A in my example is the user list.
Column G is the list items eg 1,2,3,4,5, - 40

 =AND(IF(COUNTIF($A$1:$A$10,A1)>1,FALSE,TRUE),IF(ISERROR(VLOOKUP(A1,$G$1:$G$10,1,FALSE)),FALSE,TRUE))

Thanks
Tigs

 

[ntrddragn]

thanks. im a little confused still. maybe a lot. =/

currently column d7:d50 is a list from data validation. the range for the list is o79 (range name is Staff). the amount is a vlookup formula in column 'e'. the amount is summed at the bottom of column e.

how would the formula be in the data validation formula line? =/

 

[Tigs81]

Hi ntrddragn

Sorry
I think I have miss understood what you are trying to achieve.

Just double checking that i am thinking along the same lines as you now.
For example
You have a list of Fruit each with a Cost.
You then have a order which can only cost up to 40.
The order List allows you to select a fruit from the drop down list. (Column O)
Once a fruit is selected, in Column E a vlookup is done and the cost is shown in (Column E)

At the bottom of column E is the total for the order which can only be upto a maximum of 40.

I hope that i have it right this time.



So Starting again.
I assume that you want something a bit more than a conditional format to change the cell to a different color.

So
The only way that i can think of is to use a dynamic list, which filters out the amounts that would place the list over 40.
You would need to sort the fruit in column O in descending order on Cost.

http://www.contextures.com/xldataval02.html

I could be over thinking this one though.

Thanks
Tigs

 

[ntrddragn]

Hi ntrddragn

Sorry
I think I have miss understood what you are trying to achieve.

Just double checking that i am thinking along the same lines as you now.
For example
You have a list of Fruit each with a Cost.
You then have a order which can only cost up to 40.
The order List allows you to select a fruit from the drop down list. (Column O)
Once a fruit is selected, in Column E a vlookup is done and the cost is shown in (Column E)

At the bottom of column E is the total for the order which can only be upto a maximum of 40.

I hope that i have it right this time.



So Starting again.

Thanks for the reply..thats correct. i have some thing like this but its obviously wrong.

=if(and(D:D='',E46=40),O11,if(and(D:D<>"",E46=40).O7:O9,O7:O9

what the code is or im trying to say is :if the cell/s in column d is blank and e460 is equal to 40 then they can only chose o11 (which is a blank cell) else if column d isnt blank and e46=40 then use the range otherwise just use that range.

 

[Tigs81]

do you need the drop down list? in column O

 

[ntrddragn]

do you need the drop down list? in column O

the user has to be able to pick one of four choices. so if i dont have a list, how would i make a drop down for them to pick? the result im after is when max is reach no more picking unless they go back and change something that drop the value below the max. i can make the amount stop adding but they can still pick items which is not what i want. if that makes any sense.

 

[Tigs81]

in validation, select list and in the data field place something like this.

=if(e46>=40,e47,mydropdownlist)

where e47 is a blank cell
where mydropdownlist is a namedrange of the dropdownlist items.

this option doesnt stop the user selecting an item that will place the list over 40 if the
total is under 40 eg 39 and the user selects and item that is say 2

thanks
tigs

 

[ntrddragn]

in validation, select list and in the data field place something like this.

=if(e46>=40,e47,mydropdownlist)

where e47 is a blank cell
where mydropdownlist is a namedrange of the dropdownlist items.

this option doesnt stop the user selecting an item that will place the list over 40 if the
total is under 40 eg 39 and the user selects and item that is say 2

thanks
tigs

thanks tigs but i did that and the problem with it is once it reach 40 the user cant go back to the cell and make changes. i want them to be able to make changes if they made a selection already but no be able to make new selection in the empty cell. so i wrote something like this but data validation doesnt seem to recognized it.

=IF(AND($E$64>=40,STAFF_SEL<>""),STAFF_LEVEL,(IF(AND($E$64>=40,STAFF_SEL=""),$E$65,STAFF_LEVEL)))

STAFF_SEL is range where user can select data, STAFF_LEVEL is the range for the list.