Exclude a specific value from drop down list

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

OK so how would excel know you want to exclude certain names?

Your explanation is incomplete. As Ford asked: how is Excel supposed to know that those 3 names are to be excluded? Is it always rows 6 - 8? Or are there other criteria that are used, which you have not yet told us about?

1 way to do this would be too add a helper column to pull out those names that you want, then use that column for the DD.

I put AUS in C1 (as a constant that you can change), this...
N1=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$10=$C$1,ROW($A$2:$A$10)),ROWS($A$1:A1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Use those values for the DD

Originally Posted by saurab8

Thanks for your help, but I wanted to have a table for the staff name and team names. As table makes it easy to add new names in the list.

Yes, and my suggestion wont work with that...why?

Not sure what you mean by Buddy table, but see if you can adapt this to your needs. The helper column (H) can be hidden if needed.

It may help if you upload a file that illustrates what is meant by "use multiple column value to restrict the data".
Let us know if you have any questions.

The formula that produces the list from which the drop down is populated is itself populated using:

Formula:

Please Login or Register  to view this content.

The source for the data validation is: =OFFSET(N$2,0,0,SUMPRODUCT(--(N2:N10<>"")),1)
Let us know if you have any questions.

In this case the AGGREGATE function provides an opportunity to utilize the SMALL (15) function ignoring calculation errors (6) and without having to activate the function by pressing the Ctrl + Shift + Enter keys (just press Enter).
So the formula INDEXs the names in the name column finding the first name corresponding to the conditions, AUS and Full Time, then finds the second and so on. Once all the names have been found the IFERROR function prevents the INDEX function from displaying #NUM in the remaining rows that contain the formula.
I would also suggest selecting cell N4 and running the Evaluate Formula feature (Formulas tab) and then select cell N5 and do the same to see the IFERROR function work.
Let us know if you have any questions, however if the solution and explanation are satisfactory, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.