Using Countifs in Data Validation

Hard to say without seeing a WB showing the application, but try removing the "" from "=0"
=OFFSET(Activity,MATCH(E34,Activity,0),1,COUNTIFS(Activity,E34,Available,0),1)

If you still have a problem, follow the directions in the yellow banner at the top

1. Put this in ARRAY formula M2...
=IFERROR(INDEX($J:$J,SMALL(IF(($I$2:$I$17=$B$2)*($K$2:$K$17<>""),ROW($I$2:$I$17)),ROWS($A$1:A1))),"")
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Then copy down for about 20 rows

2. change the DD in C2 to LIST
3. Use this as teh formula there...
=OFFSET($M$2,0,0,SUMPRODUCT(--(M:M<>"")),1)

The list in col M will grow or shrink to match the 0's entered in K

See attached as well

Happy to help and thanks for the feedback

Originally Posted by deepak30

If in row A, I have restricted for duplicate entry in data validation "Custom' formula =COUNTIF($A$2:A10009,A11)=1 but I also want to restrict maximum character to 30 Please guide me

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread