Peter Davison
Active Member
- Joined
- Jun 4, 2020
- Messages
- 433
- Office Version
- 365
- Platform
- Windows
Hi,
I have the formula (created in Excel 365) below which takes a list of populated and blank cells in a column (range named "DropDown_List" and sorts so all the entries are at the top and blanks at the bottom. Unfortunately the formula does not work for my colleague who has Excel 2013. (He gets the code at the beginning "xlfn" which I believe means the formula is only in Excel 365.
Can you advise if there is a formula that will do the same but can work in Excel 2013?
This is the formula created in Excel 365 -
=IFERROR(SORT(INDEX(DropDown_List, MATCH(0, IF(MAX((COUNTIF($AF$87:AF87, DropDown_List)=0)*((DropDown_List<>"")*(COUNTIF(DropDown_List, ">"&DropDown_List)+1)))=(IF((DropDown_List<>""), COUNTIF(DropDown_List, ">"&DropDown_List)+1, "")), 0, ""), 0)),1),"")
This is what shows in Excel 2013 - (Highlighted in red where it shows its not compatible with Excel 2013.
=IFERROR(_xlfn._xlws.SORT(INDEX(DropDown_List, MATCH(0, IF(MAX((COUNTIF($AF$87:AF87, DropDown_List)=0)*((DropDown_List<>"")*(COUNTIF(DropDown_List, ">"&DropDown_List)+1)))=(IF((DropDown_List<>""), COUNTIF(DropDown_List, ">"&DropDown_List)+1, "")), 0, ""), 0)),1),"")
Any help would be great.
Thanks
I have the formula (created in Excel 365) below which takes a list of populated and blank cells in a column (range named "DropDown_List" and sorts so all the entries are at the top and blanks at the bottom. Unfortunately the formula does not work for my colleague who has Excel 2013. (He gets the code at the beginning "xlfn" which I believe means the formula is only in Excel 365.
Can you advise if there is a formula that will do the same but can work in Excel 2013?
This is the formula created in Excel 365 -
=IFERROR(SORT(INDEX(DropDown_List, MATCH(0, IF(MAX((COUNTIF($AF$87:AF87, DropDown_List)=0)*((DropDown_List<>"")*(COUNTIF(DropDown_List, ">"&DropDown_List)+1)))=(IF((DropDown_List<>""), COUNTIF(DropDown_List, ">"&DropDown_List)+1, "")), 0, ""), 0)),1),"")
This is what shows in Excel 2013 - (Highlighted in red where it shows its not compatible with Excel 2013.
=IFERROR(_xlfn._xlws.SORT(INDEX(DropDown_List, MATCH(0, IF(MAX((COUNTIF($AF$87:AF87, DropDown_List)=0)*((DropDown_List<>"")*(COUNTIF(DropDown_List, ">"&DropDown_List)+1)))=(IF((DropDown_List<>""), COUNTIF(DropDown_List, ">"&DropDown_List)+1, "")), 0, ""), 0)),1),"")
Any help would be great.
Thanks