Excel Nerd pls halp

[vox nihili]

Hi there!

I'm trying to set up a Google Form for recruitment. Responses from the form are automatically saved onto a Google Sheet. I am using the IF(FIND) formula to separate the responses into the relevant teams for which the applicants have applied, then using the the IMPORTRANGE formula to set up a spreadsheet for each team leader.

So far this has worked well; however, whenever a new application comes in a new row is added to the spreadsheet. This automatically changes the IF(FIND) formula, such that that row is ignored.

For instance, if a new application comes into row 17, the IF(FIND) formula is automatically changed to consider row 18, thus ignoring the new application.

This is the formula I am using:

Code: [Select]

=IF(FIND("Biology",'Form responses 1'!$I2)<>0,'Form responses 1'!G2,"")
Thus far I have tried to use the INDIRECT function, but haven't quite managed it. I am also reluctant to use the CopyDown add on for Google Sheets because it doesn't look as though it always functions properly.