Vlookup ghost named table

[saritsky]

Hi,

Check this out, pls

1. I created an Excel file in my PC and another one in a USB Key. Just a dummy files.</SPAN>
2. I created a named table in the file in the Key</SPAN>
3. I created a VLOOKUP in the file in my PC pointing to the named table in the USB</SPAN>
4. I saved the PC file</SPAN>
5. I removed the USB Key</SPAN>
6. I closed the file in my PC</SPAN>
7. I reopened it and I went to the VLOOKUP and I changed the lookup value. I got a pop up prompting me to update the reference. I hit don’t update</SPAN>
8. Guess what? The VLOOKUP in PC file kept giving me the right values !!!! Even if the named table was no longer available!

Question; where does Excel store the table values ????


Thank you

Emilio
</SPAN>

 

[Jonmo1]

It's just "remembering" the previous valid result of the formula.

 

[saritsky]

It's just "remembering" the previous valid result of the formula.

Thank you for your answer!
You know? I think is more than that because i deleted the source file and i put some lookup values that i hadn't used before and the Vlookup gave me the right value. I don't know if i'm clear.


Example:

Source file

Named range
1, one
2, two
3, three

Workbook

=vlookup (1,Named range from the Source file, 2, false)
and i get "one" as expected

Then, i close both files, the Source and the Workbook and I delete (erase, destroy, no longer available) the Source file.
Then i reopen the Workbook and it prompt me to update the link. I hit don't update
Then I try, =vlookup (3,Named range from the Source file, 2, false)
I got a pop up prompting me to update the link and again i go for don't update
and i get "three" even if it it the first time i use that value ! You know what i mean? It's the right value ! Where is Excel getting it from?

So, no it's not "remembering" any previous vlookup results. It's recovering new values from the table. Even if the original table is no longer available.
I think the table is stored somewhere. My questions are; where it is? and how can I recover the values?

Sorry I’m not being rigorous with the syntaxes. I hope you get the concepts

Any comments?

Thank you !

 

[Jonmo1]

Wow, this is interesting...Thank you for this thread, I've learned something new.

I always assumed that the formula "rememered" the most recent result of the formula when that happens.

Apparently, the whole range is loaded into the file which contains the formula.
Where that is stored, and if it's retrieveable I don't know.

But you can even create a completely different formula, as long as it refers to the exact same range.

I did a simple vlookup as you suggested, with a 2 column range from another book.
Saved and closed both files
Deleted the source book
Opened the book with the vlookup, chose not to update links.
I then changed the lookup value and the new correct result was given.

I also created a new formula in another cell doing a simple SUM of the same range in the vlookup.
And it works.

That's pretty cool.


But alas, I have no answers about where it's stored though.
But I'm guessing it's actually loaded into the file that contains the formulas.

I would guess this is why a file's size get's bigger and bigger with the use of more and more formulas.
All those ranges are loaded and stored IN the file with the formula.


Interesting indeed.

 

[saritsky]

Wow, this is interesting...Thank you for this thread, I've learned something new.

I always assumed that the formula "rememered" the most recent result of the formula when that happens.

Apparently, the whole range is loaded into the file which contains the formula.
Where that is stored, and if it's retrieveable I don't know.

But you can even create a completely different formula, as long as it refers to the exact same range.

I did a simple vlookup as you suggested, with a 2 column range from another book.
Saved and closed both files
Deleted the source book
Opened the book with the vlookup, chose not to update links.
I then changed the lookup value and the new correct result was given.

I also created a new formula in another cell doing a simple SUM of the same range in the vlookup.
And it works.

That's pretty cool.


But alas, I have no answers about where it's stored though.
But I'm guessing it's actually loaded into the file that contains the formulas.

I would guess this is why a file's size get's bigger and bigger with the use of more and more formulas.
All those ranges are loaded and stored IN the file with the formula.


Interesting indeed.

Well......there's not such a thing like "memory", but values stored somewhere


Yes, it is pretty interesting indeed. What i'm trying to do is to create a code en VBA to pull those data, but i admit i'm a bit lost at this moment.
I used to program in other languages and it always was a way to use a "low-level language" to get inside the program, you know what i mean? Maybe i should use p-code or so, but i'm not familiar with that yet.

BTW, where is "the file that contains the formulas"? FYI, i've already tried different computers. The info resides IN the file, not in the computer.

I'll keep digging

Thank you !

E

 

[Jonmo1]

I don't know about a VBA method, but you can retrieve the table with a formula.

Say the original formula was
=VLOOKUP(2,'C:\Users\Jon\Desktop\[Book1.xlsx]Sheet1'!$A$1:$B$3,2,FALSE)

And you've deleted the Book1.xlsx
You open the book with the formula and say no to update links.

All you have to do is create a formula to retrieve the data
='C:\Users\Jon\Desktop\[Book1.xlsx]Sheet1'!A1
Fill down and right
There's the table.

but you can only retrieve data from within the table that was referenced in the original formula.
In this example I would not be able to retrieve data from A4


In VBA, I thought you might be able to use the Evaluate method
Range("A1").Value = Evaluate("='C:\Users\Jon\Desktop\[Book1.xlsx]Sheet1'!A1")

But it didn't work, I get #REF! error

 

[saritsky]

I don't know about a VBA method, but you can retrieve the table with a formula.

Say the original formula was
=VLOOKUP(2,'C:\Users\Jon\Desktop\[Book1.xlsx]Sheet1'!$A$1:$B$3,2,FALSE)

And you've deleted the Book1.xlsx
You open the book with the formula and say no to update links.

All you have to do is create a formula to retrieve the data
='C:\Users\Jon\Desktop\[Book1.xlsx]Sheet1'!A1
Fill down and right
There's the table.

but you can only retrieve data from within the table that was referenced in the original formula.
In this example I would not be able to retrieve data from A4


In VBA, I thought you might be able to use the Evaluate method
Range("A1").Value = Evaluate("='C:\Users\Jon\Desktop\[Book1.xlsx]Sheet1'!A1")

But it didn't work, I get #REF! error

You've got it ! U r the best!
Thank you, man !

E

 

[Jonmo1]

Glad to help, I learned something too.