Register
Log In
Unanswered
Home
March 16, 2017
Offline
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
See AnswerMarch 16, 2017
Offline
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<>"")))
Dashboards
June 25, 2016
Offline
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
Answers Post
March 16, 2017
Offline
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
Dashboards
June 25, 2016
Offline
Hi Craig
There is no attachment.
March 16, 2017
Offline
Apologies Sunny, I have been away.
Please see work book attached
Dashboards
June 25, 2016
Offline
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
Most Users Ever Online: 57
Currently Online: Mark Lund, Max Neumann
22 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Ajay Mukhi
Stephen Motte
Rodney Hoyle
michael pitre
Samantha Burleson
Krishnaa Kurugod
David McSwain
Shameem Nassiree
Alanna Henderson
Janel Oz
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
