Excel VLOOKUP function
Is there any way where we can copy the values during vlookup and paste them in a location and makes the values exist even when the reference sheet gets deleted.
Select the range and copy
While range is selected, right-click and paste Values
While range is selected, right-click and paste Values
Can you please re-phrase your question or be more specific, in case Shawn's answer is not what you are looking for ?
gowflow
gowflow
Normally, your spreadsheet should retain the calculated / looked up value, even if the source is no longer accessible; unless you edit and re-commit the formula.
The options are:
1. You want to keep the formulas so that the sheet will recalculate when a new source document is provided in the specific location.
2. Break the formula when recalculation is not necessary
The options are:
1. You want to keep the formulas so that the sheet will recalculate when a new source document is provided in the specific location.
This way, do not recalculate the sheet until a new source is provided with the same file name, same sheet name, stored in the same location
2. Break the formula when recalculation is not necessary
If you know all the ranges with the lookup formulas, you can use either Shawn's method to paste the calculated value as value.
Or, if the lookup formulas are scattered all over the place, and you don't want to accidentally miss some, you can do the following.
Open File menu, then look for "Edit Links to Files" and open it.
In the pop up window, Edit LInks, you can check the status of all linked files. If the status is unknown, or Error: Source not found, you can use the Break Link button to turn all related cells into values, without knowing where the formulas are.
Or, if the lookup formulas are scattered all over the place, and you don't want to accidentally miss some, you can do the following.
Open File menu, then look for "Edit Links to Files" and open it.
In the pop up window, Edit LInks, you can check the status of all linked files. If the status is unknown, or Error: Source not found, you can use the Break Link button to turn all related cells into values, without knowing where the formulas are.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
But, you can develop your own vlookup function.
Warning tho: Depending on the amound of data, performances may severly suffer.