IBP - Formula to list filters

[willsuus]

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

[willsuus · Accepted solution]

I was able to put together a formula that works to list both workbook and worksheet filters without the extra line feeds. This is very basic and can be enhanced as needed.

= "WORKBOOK Filters:" & CHAR(10)

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

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

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

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

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

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

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

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

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

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

& IF(SOP_WB_Filter_Criteria_Count > 10, "+ Additional Workbook Filters" & CHAR(10),"")

& CHAR(10) & "WORKSHEET Filters:" & CHAR(10)

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

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

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

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

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

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

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

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

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

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

& IF(SOP_Filter_Criteria_Count > 10,"+ Additional Worksheet Filters","")