In the attached file new lists are produced for each category without the asterisks in columns I:O on the Sorting Data sheet.
Each list is also a named range with the exception of New Form which has no formatting.
A helper column (S) has been added to the Paste Invoice Here sheet.
The helper column may be moved and/or hidden for aesthetic purposes and is populated using:
Formula:
=IFERROR(INDEX('Sorting Data'!I$1:O$1,AGGREGATE(15,6,(COLUMN('Sorting Data'!I$1:O$1)-COLUMN('Sorting Data'!H$1))/('Sorting Data'!I$2:O$86=J17),1)),"")
Column J on the Paste Invoice Here sheet is conditionally formatted using rules like: =COUNTIFS(Non_Form,J17)
The lists of medications on the Results sheet are populated using:
Formula:
=IFERROR(INDEX('Paste Invoice Here'!$J$17:$J$82,AGGREGATE(15,6,(ROW('Paste Invoice Here'!$J$17:$J$82)-ROW('Paste Invoice Here'!$J$16))/('Paste Invoice Here'!$S$17:$S$82=B$1),ROWS($A$1:$A1))),"")
The Total Cost is populated using: =SUMIFS('Paste Invoice Here'!$P17:$P82,'Paste Invoice Here'!$S17:$S82,B1)
The Total Count is populated using: =COUNTIFS('Paste Invoice Here'!$S17:$S82,B1)
Let us know if you have any questions.
Bookmarks