Question also posted here:
I have a few macros that perform various tasks, and it seems rather pointless that the final step of the process requires the user to manually enter the formula, so here is the scenario:
I have 2 spreadsheets:
1. Consolidated Sheet - Contains an employee number and the employee details (See image below)
2. Summary Sheet - Contains only 3 columns; AppName, EmpNum, Status (See image below)
Note: There is currently no data on any of these sheets, and on the summary sheet, I create a table from the data so that the formula will work
On the consolidated sheet at the end of the column "Supervisor Email", there are a few more columns containing the application names (See image below)
Originally, the idea was to lookup the employee number and app name with the below formula, then copy the status to the relevant cell - but this formula does not seem to be working AND it requires the user to insert the formula manually into each cell under every application name.
=IFERROR(INDEX(Table2[Status],SUMPRODUCT((Table2[App Name]=L$1)*(Table2[Employee Number]=$A2)*(ROW(Table2[Status])))-1,1),"")
Now, what I am looking to do is relatively the same thing, but with 2 differences:
1. I don't want the status to show in the cell the formula is placed into - If there's a match, then it should display "MATCH" and if there is no match then the cell should be blank
2. I need this formula to be in a macro that finds the "lastRow" and populates across the columns from App1 - to the last AppName and down to the "lastRow"
Sample workbook attached.