Skip to Content
0
Sep 11, 2023 at 03:21 PM

IBP - Formula to list filters

86 Views

The formula we currently use to list filters lists only the worksheet filters. I would like to combine both the WORKBOOK and WORKSHEET list filter formulas into one but when I try to, it includes all of the line breaks even when there is no more filter criteria leaving a large break between the two lists (see attached). Has anyone determined a way to exclude these extraneous breaks?

="WORKBOOK Filters:"&"

"&IFERROR(@INDEX(SOP_WB_Filter,1),"")&"

"&IFERROR(@INDEX(SOP_WB_Filter,2),"")&"

"&IFERROR(@INDEX(SOP_WB_Filter,3),"")&"

"&IFERROR(@INDEX(SOP_WB_Filter,4),"")&"

"&IFERROR(@INDEX(SOP_WB_Filter,5),"")&"

"&IFERROR(@INDEX(SOP_WB_Filter,6),"")&"

"&IFERROR(@INDEX(SOP_WB_Filter,7),"")&"

"&IFERROR(@INDEX(SOP_WB_Filter,8),"")&"

"&IFERROR(@INDEX(SOP_WB_Filter,9),"")&"

"&IFERROR(@INDEX(SOP_WB_Filter,10),"")&"

"&IFERROR(@INDEX(SOP_WB_Filter,11),"")&"

"&IFERROR(@INDEX(SOP_WB_Filter,12),"")&"

"&IF(SOP_WB_Filter_Criteria_Count <=13, IFERROR(@INDEX(SOP_WB_Filter,13),""),SOP_WB_Filter_Criteria_Count-12&" more...")&"

"&"WORKSHEET Filters:"&"

"&IFERROR(@INDEX(SOP_Filter,1),"")&"

"&IFERROR(@INDEX(SOP_Filter,2),"")&"

"&IFERROR(@INDEX(SOP_Filter,3),"")&"

"&IFERROR(@INDEX(SOP_Filter,4),"")&"

"&IFERROR(@INDEX(SOP_Filter,5),"")&"

"&IFERROR(@INDEX(SOP_Filter,6),"")&"

"&IFERROR(@INDEX(SOP_Filter,7),"")&"

"&IFERROR(@INDEX(SOP_Filter,8),"")&"

"&IFERROR(@INDEX(SOP_Filter,9),"")&"

"&IFERROR(@INDEX(SOP_Filter,10),"")&"

"&IFERROR(@INDEX(SOP_Filter,11),"")&"

"&IFERROR(@INDEX(SOP_Filter,12),"")&"

"&IF(SOP_Filter_Criteria_Count <=13, IFERROR(@INDEX(SOP_Filter,13),""),SOP_Filter_Criteria_Count-12&" more...")workbook-and-worksheet-filter-lists-combined.png