Vlookup range not updating with form submissions

Francesca O'Loughlin

I have a sheet with a list of employees that references form responses on a different sheet, and via vlookup function pulls in the date a form was submitted (to track who has submitted the form, and who as not yet done so). The problem I'm having is the vlookup range automatically limits itself to the forms already submitted, even when drag down past submitted rows. So whenever new rows are added I have to manually update the formula to incorporate the new rows. has anyone found workaround for this?

Andrée Starå

Hi Francesca,

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Also, can you share the formula(s) you're using?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

If the formula is on the same sheet as the form entries, repeating the column name with no row numbers will reference the entire column.

[Column Name]:[Column Name]

If it is a cross sheet reference, you can reference the entire column by clicking on the column header instead of selecting specific cells.

The above would be two ways to reference an entire column to be able to take into account new rows being added.

Francesca O'Loughlin

thanks for the feedback, unfortunately selecting the full column doesn't solve the problem, the formula automatically limits itself to the bottom row of data on the sheet.

to flesh out the process a bit more: we have a process of interviewing staff who are leaving our organization to get their feedback on employment (highlights, what we can do better, what their reasons for leaving were, etc.) this data is obviously sensitive so it is only shared with HR, however we want to notify their manager and other members of the organization when their exit interview has been completed, so on a different sheet that tracks the departing staff process we use a vlookup to pull in the date the exit interview form was submitted.

I think the problem arises with it being a form submission for the interview rather than a standard grid, whenever a new row is created through a form being submitted it cuts off the vlookup formula to the row directly above it, so I have to go in repeatedly and update the range to include the new rows.

the formula is vlookup (staff email column on the main sheet, range of departing staff interview submissions on the departing staff interview sheet, column with the date, false)

Paul Newcome

You should not have to update the formula to include new rows if you are referencing the entire column by not specifying any row numbers at all in your range. What is the exact formula you are using?

Andrée Starå

@Francesca O'Loughlin

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée