Drop-down list with repeat criteria

Asked

Viewed 1,312 times

4

I have a conference list, where in Plan1, column A', will be typed only the cods that are in Plan2, column A' more with a criterion can only repeat the total of times that is in plan2.

    1. To type in Plan1, only the Plan2 cods I used a Data Validation (disabling the drop-down list) when typed a Cod that is not in plan2 returns an error msg. ae Ok.

My problem is that on Plan1, I can endlessly repeat if I pass the list criteria, I need Plan1 cods to repeat only as many times as I’m on plan2, column B'.

Plan1: Column A: Cod input (repeat, per 'X' Times)

Plan2: Column A: Cod (not repeated) Column B: Qtd (total of times Cod, can be repeated in Plan1)

Imagery: https://i.stack.Imgur.com/Utvn2.png

excel vba

1 answer

1

Let’s change the source for its validation. In Plan2, in column C, you will include a "balance", which will make the difference between the maximum limit in column B and the counting of occurrences already inserted. 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 column A, but replacing empty ones that have already reached their limit. Then you change the validation in Plan1, to use Plan2! D as the checklist.

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.