Data Validation Error

[piperjo]

I am trying to create a drop down for scheduling and I want it to hide names once they are clicked. I have followed all the instructions to a T. Once I get to the validation and enter the name range I get the error "Source currently evaluates to an error. I have window 10pro I am not sure what to do next.

 

[jasonb75]

Once I get to the validation and enter the name range I get the error "Source currently evaluates to an error.

You might find that this happens when you set it up and the cell to be validated is still empty. If you ignore the error message and continue then it could still work.

That's best guess without seeing the 'instructions' that you have used.

 

[piperjo]

here is what i have done How to hide previously used items in dropdown list? (extendoffice.com)

 

[jasonb75]

I don't see any reason for the error in the guide. I've set up a test sheet and don't get an error so you may have missed something. As I can not see your screen, it is impossible to say what you may have missed.

Try changing the formula in column B to this one (steps 1 and 2 in the link)

=IFERROR(INDEX($A$1:$A$11,AGGREGATE(15,6,ROW($A$1:$A$11)/ISNA(MATCH($A$1:$A$11,$F$1:$F$11,0)),ROWS($B$1:$B1))),"")

Ignore steps 3 and 4 (delete the formulas from this step if you have already entered them)
Continue from step 5 onward using this formula in place of their suggestion for step 6

=$B$1:INDEX($B$1:$B$11,COUNTIF($B$1:$B$11,"?*"))

** Note that I have not used the sheet name in the second formula, excel will add this in automatically. Make sure that you have the correct sheet selected when you complete step 6! **

Note that this method (and the one in the link) create the dropdown in the same order as the original list.. You can sort the list with the formulas if required but it becomes much more complicated when working with anything other than an up to date version of office 365.

Book1 (version 1).xlsb
	A	B	C	D	E	F
1	James	James				Andrew
2	Max	Max				Jessie
3	Jone	Jone
4	Michael	Michael
5	Andrew	Phillip
6	Phillip	Tom
7	Jessie	Peter
8	Tom	Micheal
9	Peter	Anny
10	Micheal
11	Anny
Sheet5

Cell Formulas
Range	Formula
B1:B11	B1	=IFERROR(INDEX($A$1:$A$11,AGGREGATE(15,6,ROW($A$1:$A$11)/ISNA(MATCH($A$1:$A$11,$F$1:$F$11,0)),ROWS($B$1:$B1))),"")

Named Ranges
Name	Refers To	Cells
namecheck	=Sheet5!$B$1:INDEX(Sheet5!$B$1:$B$11,COUNTIF(Sheet5!$B$1:$B$11,"?*"))	B1:B11

Cells with Data Validation
Cell	Allow	Criteria
F1:F11	List	=namecheck

 

[piperjo]

I got the same error, I can upload the spreadsheet if that would help.

 

[jasonb75]

Have you adjusted the formula ranges to match your own data layout?

Are the formulas in your sheet duplicating the list as expected?

 

[piperjo]

I can do the very basics in excel, so I am afraid that I can't answer that

 

[jasonb75]

OK, approaching the task from a different angle.

Where is your original list of names? I need the sheet name and the range, for example Sheet1 A1:A10.

Where are your data validation dropdowns going? As above, I need the sheet name and the range.

 

[piperjo]

Names: Sheet A2:A51
Drop Down: Sheet G2:G51

 

[jasonb75]

You will need to enter this formula into B2, then fill it down to B51 as per step 2 in the link.

=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$51)/ISNA(MATCH($A$2:$A$51,$G$2:$G$51,0)),ROWS($B$2:$B2))),"")

Then use this one for the named range definition (the 'Refers to:-' box) as per step 6 in the guide.

=Sheet!$B$2:INDEX(Sheet!$B$2:$B$51,COUNTIF(Sheet!$B$2:$B$51,"?*"))

As before, steps 3 and 4 can be omitted. The rest should be done as per the guide with the formulas changed to those above.