Prev: Copy sheet to new sheet
Next: Using Popups to supply variables
From: JLatham on 31 Jan 2010 10:18
OK, late note and not sure it adds any real value for you or not. I see that
[#All] is created as a reference to a Table defined rather than a normal
named range. That explains my confusion (old school <g>). So I was able to
set up a formula just like yours and it works whether the DATA BOOK is stored
on a local drive or one of my network drives, and whether or not that book is
open.

But one thing I found was that if I edited the formula and removed the
[#All] portion, that I got a #REF error, and just typing it back in did not
clear up the #REF error until I re-opened the DATA BOOK, at which time it
would figure things out again. So perhaps opening up DATA BOOK and
re-entering the formula may help?

"zxcvbnm6000" wrote:

> @Bob Bridges - It is on the same PC and same HDD.
>
> @ JLatham - I copied the equation correctly. Data[#All] refers to the table
> "Data" and [#All] indicates that it includes the whole table not just pieces.
> I found it odd too, that there is no sheet name, but if I try to enter it,
> Excel just updates the equation to what I pasted.
From: zxcvbnm6000 on 31 Jan 2010 15:50
@ JLatham - Removing the [#All] will not work, as you found out, since it is
the key reference to what part of the table to use. The thing is you wrote
that when you opened up the DATA BOOK again the #REF! errors go away, which
is true. But if you close it again, save the book with the VLOOKUP equations,
close, then reopen, the errors are right back again. For some reason the data
will not save and will not update from the DATA BOOK without having it open.
It is just weird.

I do have another book where I use VLOOKUP that is updating fine, so I'm
going to study the equation to see where the difference is. Hopefully I can
figure something out.
From: zxcvbnm6000 on 4 Feb 2010 21:25
So I looked into this more and found if I use the sheet name and the A1:C10
style reference it works, but using a table name reference doesn't. Is this a
glitch or something? I can't figure out why these two things aren't the exact
same thing.

Also to completely confuse me, I found the table name reference works
sometimes when embedded in a certain equations, but not all of them.

If someone can explain this to me I'd love to hear it.
First  |  Prev  | 
Pages: 1 2
Prev: Copy sheet to new sheet
Next: Using Popups to supply variables