Drop-down list with repeat criteria

Question

Drop-down list with repeat criteria

Navigation

#1 by (1 votes)

4

I have a conference list, where in Plan1, column A, only the codons that are in Plan2, column A, will be typed, but with a criterion, only the total number of times in plan2 can be repeated. >

To enter in Plan1, only the cods of Plan2 I used a Data Validation (deactivating the drop down list) when typed a cod that is not in plan2 returns an error msg. bye and bye.

My problem is that in Plan1, I can repeat infinitely if I go through the criterion of the list, I need the cods of Plan1, to repeat only the times that are in plan2, column B '.

Plan 1: Column A: Cod entry (repeats, 'X' times)

Plan2: Column A: Cod (do not repeat) Column B: Qty (total number of times the cod can be repeated in Plan1)

Picture: link

excel vba

asked by anonymous 30.08.2014 / 03:09

1 answer

Error in Chrome when decoding accents posted via HTTP POST Sense / NET: Installation and programmatic configuration

score 1 · Answer 1

Let's change the font for your validation. In Plan2, in column C, you will include a "balance", which will make the difference between the upper limit in column B and the count of occurrences already entered. For example, include this formula in Plan2! C3 and following:

=$B3-CONT.SE(Plan1.$A$3:$A$1000;$A3)

In Plan2! D3 onwards, you include

=SE($C3>0;$A3;"")

In this way, column D will have the same codes as in column A, but instead replace those that have already reached their limit. Then you change the validation in Plan1, to use Plan2! D as the validation list.