Column Linking - not picking up new rows



I’m having trouble linking a ‘RowID’ column (Auto number) in my master ‘Project Intake’ sheet to a target or secondary ‘Sponsor’ sheet column named ‘Sponsor Row ID’. I use the two Row ID columns to successfully pull data using INDEX/MATCH which all works flawlessly.

I use a form to enter new projects (new row) onto the Project Intake sheet. The form does what it should do and adds a new row below the last existing row. The auto number function in the RowID column also self populates correctly.

However the linked Sponsor Row ID of the Sponsor sheet does not automatically pick up the new row in the Project Intake sheet. Try as I may I cannot link to blank rows (as I guess they don’t exist yet?) below the last row of the Project Intake sheet.

How can I overcome this restriction. It’s causing me grief as my automated workflows and summary reports all fail to pick up the new project data.

Thanks Colin