Is there a way to NOT update the results of a VLOOKUP formula if the lookup table is updated?
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!
Best Answer
-
@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.
Answers
-
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
-
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
-
@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.
-
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
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!