Is there a way to NOT update the results of a VLOOKUP formula if the lookup table is updated?

Adam F

Hello,

The Smartsheet Community has provided me with loads of help for years and this is the first question I have posted. I really appreciate all of the help and attention to detail provided here. Thanks in advance for thinking through my question!

I have a simple VLOOKUP formula that the department value within in the same sheet (a dropdown list of departments) to find the corresponding manager in a separate lookup sheet. It works without any issue.

=VLOOKUP(Department@row, {Lookups}, 2, false)

In my sheet, this formula is returning a manager name into a column showing who made an approval. I just noticed that if I update the lookup table ALL references to that manager in the primary sheet are updated too. This makes sense, and I can see how this can be helpful in many scenarios, but I'm looking for a way to find the manager name, return it to the primary sheet, and have it NOT update if I make changes to the lookup table. I am using this column as a historical reference and need it to be static.

Maybe there is an alternative way to accomplish this instead of VLOOKUP? Any help is appreciated, thanks!

Paul H

@Adam F @Kelly Moore @David Tutwiler

I have a similar issue with people changing positions.

Would copying the row to another sheet work in such a situation?

Form submissions go to Sheet A, cross reference the Name triggering a copy row to Sheet B which becomes the primary working sheet.

David Tutwiler

I don't think there is a way to do this with formulas. However, there is a Smartsheet app called DataMesh that allows you to control what happens when the Source sheet updates. You can update everything or leave things the way they are and only update new entries. Below is the landing page for that product.

https://www.smartsheet.com/marketplace/premium-apps/datamesh

Kelly Moore

Hey @Adam F

One build to David's answer. In addition to DataMesh, I do exactly what you're suggesting for your exact reason using the premier App Bridge.

Kelly

Paul H

@Adam F @Kelly Moore @David Tutwiler

I have a similar issue with people changing positions.

Would copying the row to another sheet work in such a situation?

Form submissions go to Sheet A, cross reference the Name triggering a copy row to Sheet B which becomes the primary working sheet.

Kelly Moore

Hey Paul

I think your approach will work since the Move/Copy rows only copy values and not formulas. That's a clever way to force the dissociation from the formula that is linked to the lookup table.

Kelly

Adam F

Thanks @Paul H. That is precisely the solution I went with. Try as I might, I could not find any work around within the same sheet so I use a copy row automation and continue my work from a second sheet. It actually worked out better for the long run because the next rounds of automation perform better in an isolated environment.

I appreciate everyone's thoughts and help. Thank you!