
Originally Posted by
Pete_UK
In the attached file I have used column O in Sheet1 as a helper column (coloured blue), with this formula in O2:
=IF(OR(H2="",I2=""),"-",IF(ISNUMBER(--SUBSTITUTE(H2&I2,"/","")),MAX(O$1:O1)+1,"-"))
This is then copied down beyond your data to accommodate more data being added - the hyphens indicate how far the formula is active. The formula identifies the records which meet the criteria and gives each a unique sequential number.
Then in Sheet2 I have used column A as another helper, with this formula in A2:
=IFERROR(MATCH(ROWS($1:1),Sheet1!O:O,0),"")
The formula finds the rows in Sheet1 where the records meet the criteria. B2 in this sheet contains this formula:
=IF($A2="","",IF(INDEX(Sheet1!B:B,$A2)="","",INDEX(Sheet1!B:B,$A2)))
which returns the appropriate data, and this formula is copied across to Column K to get successive fields. Finally, the row of formulae from row 2 is copied down as far as you think you might need them (to row 10 in this case).
The formula-based solution will respond immediately to changes in Sheet1 - set I4 or I6 to a number to see the effect on Sheet2, or set H5 to a number.
Hope this helps.
Pete
Bookmarks