Input New Statements Row on Top Breaks Range Lookup

the example sheet image on the explanation thread for the statements sheet (Bank Statement Reconciliation Sheet) has the newest statement on top, which is best to have newest info on top like the transactions sheet for when the sheet grows. only thing is that on my sheet when I insert a new row on top, it screws up the matches (images attached) how can I avoid this?

One issue that I often run into when inserting rows is that adding a new row at top or bottom can mess up a range lookup. For example, if the Transactions sheet is getting data validation from Statements!A3:A and you add a new row above A3, this can update all references to the range A3:A to become A4:A. In this way, your new row may fall out of the lookup range.

For me, the easiest solution is to add my new row within the ranged lookup (e.g. add it below A3). In this way, the range lookup doesn’t change. I think you can later drag your new row to the top of the range without affecting the range lookup.

Make sense?

1 Like