Hi everyone, I am a first time poster because I have this headache of a report template I am trying to build and desperately need assistance.
Report1 is a large report that is produced every fortnight.
ReportTemplate is a small summary report that I am designing to pull specific data from Report1. I currently have formulas which are easy to use the search and replace function to immediately pull results from the updated report each fortnight. I do not wish to use VBA.
ReportTemplate will need to search column AM of Report1 for all rows containing "Customer Services". I have put this text in N5 of ReportTemplate. It will then need to search Report1 in column AE that contain also DO NOT contain "N/A". I have put this text as N6 in report template.
My current function looks:
=IF(LEN($N$5),IFERROR(INDEX('[Report1.xlsx]Sheet1'!AM$1:AM$999,SMALL(INDEX(ROW($1:$999)+('[Report1.xlsx]Sheet1'!$AM$1:$AM$999<>'[Report1.xlsx]Sheet1'!$AM32)*1E+99,,),ROW(1:1))),""),"")
This function is working for the first criteria, however I cannot figure out how to add the second in. That is, it is finding and displaying all entries that have "Customer Services" in column AM, but then is not filtering further to only show those that also DO NOT display "N/A" in column AE. How can I add this criteria in?
Thanks in advance for your help!!!
Bookmarks