List unused items from data validation list without blanks

Hi All,

I am looking for a way to list the names remaining from a data validation list that have not yet been used in the range the data validation applies to, without blanks.

To clarify further -

On a staff schedule sheet, data for validation is sourced from a named range called DataValidation and is a list of staff names.
The range the data validation applies to is D5:D52 - a staff schedule column for a day in the week.
Starting from cell D54, I would like to list the names not yet used in D5:D52 as a prompt to users which names they have left to utilise on the schedule for that day.

Any help would be greatly appreciated.

Regards,

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

You probably want something like this.
The complete list of names is in Column K
The list of names that is used as DataValidation is in E

Putting an arrayed formula into E2 and copying down

=IF(COUNTA($K$1:$K$35)-COUNTA($A:$A)>=ROWS($A$1:$A1),INDEX($K$1:$K$35, SMALL(IF(ISNA(MATCH($K$1:$K$35, $A$1:$A$35,0)),ROW($K$1:$K$35)),ROWS($A$1:$A1))),"")

Then the data validation in Column A is a list = Datavalidation.

I know there is an easier way to create the gradually reducing list with LOOKUP but can't remember it off the top of my head.

Hi ChemistB,

Thanks for your input but I do not seem to be able to get results I think due to the layout of the work sheets in my workbook. I've attached a sample workbook as suggested by mehmetcik and hope that this explains the requirement better.

In the attached workbook, on the 'Schedule' sheet, the validation list for range D5:D5 is sourced from the named range called ValidationList in the 'Staff Data' sheet. ValidationList is the combination of the StaffList and Management named ranges. On the 'Schedule' sheet I have set up two examples in the MONDAY and TUESDAY sections of the Schedule. The names from StaffList, found in the Staff Data worksheet, that are not yet used for range ("D5:D52") in the Schedule are recorded from row 54 down in each staff column. It is this list from row 54 downwards that I am trying to populate. The list should update automatically (become smaller) as names included in StaffList are added to range D5:D52.

I hope this explains it better.

Regards,

Hi ChemistB,

I have reviewed your solution again to see if i could make it work and although very useful where the requirement is for a reducing validation dropdown list, it does not fit the requirement in my situation.

To explain better I have started from scratch with a new example sheet, attached. The area designated "Sheet 1" has two lists, Staff List and Management that are combined to make Validation List. "Sheet 2" is a staff schedule for a single day with the Scheduled Staff validation list sourced from Validation List in Sheet 1. The yellow highlighted names at the bottom of the Scheduled Staff list is a list of the staff not yet used in the list above it excluding those names on the Management list.

Names can be used more than once in the Scheduled Staff list. I only need to list in the highlighted yellow area, those names not yet used from Staff List on Sheet 1. Names from the Management List are to be ignored. The yellow area will contract as names are added.

Any solution would be amazing!

Many thanks in advance,

This was solved in a new thread here http://www.excelforum.com/excel-form...on-column.html

The new thread explained things alot better as this thread got lost along the way (entirely my fault!)

Many thanks to lecxe for the assistance.

Regards,

Glad you found a solution.