Copy data from one worksheet to another worksheet based on criteria

You can do this using 3 formulae and a little table. In the attached file I've inserted a new column A and set up a table in columns M and N of the main sheet, showing the transaction types and the sheet that you want them to be copied to. I've put this formula in A2:

=IF(D2="","-",VLOOKUP(D2,M:N,2,0)&"_"&COUNTIF(A$1:A1,VLOOKUP(D2,M:N,2,0)&"*")+1)

which identifies the sheet the record should be copied to and gives it a unique sequential number. The formula should be copied down beyond your data, to accommodate new data being added (I've copied to row 100, as can be seen by the hyphens).

In the subsidiary sheets I've also inserted a new column A, and I've copied your data across to column M so you can compare the results of the formulae. In each sheet I have put the name of the sheet in cell A1 (also coloured with a yellow background) and this formula in A2:

=IFERROR(MATCH(A$1&"_"&ROWS($1:1),'BG -1'!A:A,0),"-")

This identifies the row in the main sheet where that record occurs. I have used this formula in B2:

=IF(OR(A2="",A2="-"),"",INDEX('BG -1'!B:B,$A2))

which is copied across to I2 to bring the relevant data from the main sheet. Then this row of formulae have been copied down (to row 40), to give you the results you are after. All the helper columns (column A - shown in blue) can be hidden if you want the sheets to look the same as before.

Hope this helps.

Pete