Odd Formula errors

Kelly O'Neill-DiCarmine

I have an automated request sheet that we've use for over a year.

Example of a request from an entry on line 2: =VLOOKUP(Requestor2, {Contact Sheet Range 2}, 5, false) - it looks up the requestor (lrequestor from line 2/"requestor2), and cross references the "contact sheet" to find their supervisor (5) and then sends the request for approvals.

Suddenly, it's changing the formula on it's own: - this is an example of a form entry that is on line 1 - =VLOOKUP(Requestor38, {Contact Sheet Range 2}, 5, false) it's pulling the requestor from line 38.??

This is also happneing on another calculating sheet - it's pulling lines well below and not calculating correctly -

HELP!

Genevieve P.

Hi @Kelly O'Neill-DiCarmine

I would suggest using @row instead of a row reference. @row will make sure that the formula is always looking at the cell on this row and it won't auto-adjust numbers as your rows are adjusted:

=VLOOKUP(Requestor@row, {Contact Sheet Range 2}, 5, false)

See: Create Efficient Formulas with @cell and @row

If you're looking to instead lock the formula to the cell on row 2, you can use an absolute reference instead:

=VLOOKUP(Requestor$2, {Contact Sheet Range 2}, 5, false)

See: Create a Cell or Column Reference in a Formula

Cheers,

Genevieve