Hi,
I would like to create a validated list that ignores cells with formulas that returns zero length strings?
I have already used the formula below to remove blank cells from the list but the validated list still includes blanks.
=IFERROR(INDEX($A$3:$A$100, SMALL(IF(LEN($A$3:$A$100)=0,"", ROW($A$3:$A$100)-MIN(ROW($A$3:$A$100))+1), ROW(A1))),"")
I have attached the work book for reference. The validated list is used in the 'Engineer' column on the 'Aylesbury' sheet.
Craig
Forgot to add that I am using the formula in the defined list.
='Reference Sheet'!$B$3:INDEX('Reference Sheet'!$B$3:$B$1000,SUMPRODUCT(--('Reference Sheet'!$B$2:$B$1000<>"")))
Hi CK_One
Try this
I have created a dynamic range named MyList and used it in your DV.
Change to suit your needs.
Hope this helps.
Sunny
Thanks Sunny, works perfectly
Going off the topic, I would like the name list to be alphabetically sorted.
I tried the formula below which seemed to work up until the 8th Name but then returns #NUM! for the 9th and 10th name, not sure why this is.
=IF(COUNTA(NameList)>=ROWS($C$3:C3),INDEX(NameList,MATCH(SMALL(COUNTIF(NameList,"<"&NameList),ROW(B3)),COUNTIF(NameList,"<"&NameList),0)),"")
Any ideas?
Workbook attached
Hi Craig
There is no attachment.
Apologies Sunny, I have been away.
Please see work book attached
Hi Craig
Perhaps you can try this. In cell C3 of ther Reference sheet, enter the following formula (array entered) and then drag down as many rows as you need. I have added an error checking to show blanks instead of an error.
=IFERROR(INDEX(NameList, MATCH(SMALL(COUNTIF(NameList, "<"&NameList), ROW(1:1)), COUNTIF(NameList, "<"&NameList), 0)),"")
Hope this helps.
Sunny
Hi Sunny,
That worked perfectly, thanks for your help much appreciated.
Craig
