Forum Discussion
Copy data from 2 sheets to others based on criteria
I honestly cannot decide whether to be deeply impressed by your formulae and their layout or horrified at the choice of tools selected for the job!
In PowerQuery all that is required is to append the input tables and filter to give the output tables. I appreciate that you want a more dynamic behaviour and so are attracted to tables. If so, I suspect that this one workbook by itself would justify a copy of Office 365 to gain access to the new dynamic array functionality (at the moment only 'insider' versions but expected for wider release any time now).
The formula I used to combine your salesperson data was
= CHOOSE( s#,
INDEX( Jack, recordNum#, columnNum# ),
INDEX( Liam, recordNum#, columnNum# ) )
This table has named sub-ranges 'combinedBranch' and 'combinedData' giving the final formula
= FILTER( combinedData, combinedBranch="George St" )
on your George St sheet.
Each formula occupied a single cell but they spill to fill their respective table.