how to retain a fetched value from another workbook that get's moved and/or renamed

Hi gang

Still very new to learning excel, so this may be a very dumb question for most of you.

Im working with two work books, located over a network on different computers.

I'm currently using this formula in my "Book1" to pull data from "Book2"
=VLOOKUP(A1,[Book2]Sheet1!$A$1:$B$10,2,FALSE)
I could also use
=INDEX([Book2]Sheet1!$B$1:$B$11,MATCH(A1,[Book2]Sheet1!$A$1:$A$11,0)) Suggested by jeffreybrown when I was having an Issue.

But these link to the data. So if the data in Book2 changes, or the book itself gets moved or renamed/deleted. I'll lose the data in Book1.

Is there a "pull the current data and Keep it permanently" formula ?
Like a Ctrl+c then Ctrl+V but in a cell formula veriant

Cheers

Hello -

To my knowledge, there is no way to make data referenced from one spreadsheet into another a permanent piece of data. The nature of formulas is they always update and return whatever value they are designed to return. So, even though a piece of data shows up in your spreadsheet, it is just a reflection of where that data came from, not the actual data itself. There might be a VBA way of doing this, but it kind of defeats the purpose of a formula because you typically want the formula to refresh so you are working with current data.

The best way to approach this is to not move/rename files. We have this same issue with engineering files in AutoCAD. When a project is completed and several years old, we archive it to a different drive. That means all the links within each file are now looking in the wrong place. You just have to go find those files and edit the links to the new file name/location.

Sorry I couldn't be more helpful. Thanks for the Rep!