I want to create a table that will only pull certain entries from a large report. This report is updated fortnightly so it needs to be quick and easy to update the functions. For others I have found the search and replace tool to work well for the name of the source document.
This is what I currently have:
=IF(LEN('[REPORT1_20200703.xlsx]Sheet1'!$AM32),IFERROR(INDEX('[REPORT1_20200703.xlsx]Sheet1'!H$2:H$999,SMALL(INDEX(ROW($1:$999)+('[REPORT1_20200703.xlsx]Sheet1'!$AM$1:$AM$999<>'[REPORT1_20200703.xlsx]Sheet1'!$AM32)*1E+99,,),ROW(1:1))),""),"")
This formula is currently checking for my first criteria, but not my second criteria.
I want it to search ALL entries in column AM to find only those that look like "Customer Services" (this is currently in AM32, but thinking I should use an absolute reference from my main workbook, as that may change each fortnight) and also those that DO NOT have "N/A" in column AE. Then I want it to display ALL relevant entries & the data from those row, but as follows:
This is what I currently have:
=IF(LEN('[REPORT1_20200703.xlsx]Sheet1'!$AM32),IFERROR(INDEX('[REPORT1_20200703.xlsx]Sheet1'!H$2:H$999,SMALL(INDEX(ROW($1:$999)+('[REPORT1_20200703.xlsx]Sheet1'!$AM$1:$AM$999<>'[REPORT1_20200703.xlsx]Sheet1'!$AM32)*1E+99,,),ROW(1:1))),""),"")
This formula is currently checking for my first criteria, but not my second criteria.
I want it to search ALL entries in column AM to find only those that look like "Customer Services" (this is currently in AM32, but thinking I should use an absolute reference from my main workbook, as that may change each fortnight) and also those that DO NOT have "N/A" in column AE. Then I want it to display ALL relevant entries & the data from those row, but as follows:
| B | C | D | E | F | G | H | I | J | K | L |
| Display: Report 1, AM | Display: Report 1, H | Display: Report 1, G | Display: Report 1, K | Display: Report 1, AB | Display: Report 1, BF | Display: Report 1, E | Display: Report 1, Z |
