vlookup table array question

[sunrise06]

When using vlookup that references a table array in a different file saved in a shared drive folder, does that file have to be open for the vlookup to work?

 

[Aladin Akyurek]

VLOOKUP would ordinarily work with closed files.

 

[sunrise06]

What do you mean "would ordinarily work with closed files"? So it does not always work with the file closed?

 

[Aladin Akyurek]

What do you mean "would ordinarily work with closed files"? So it does not always work with the file closed?

It does work with closed books. Your question (also) reads as if there would be somehow a problem in your environment. Hence, the cautious tone in my reply.

 

[sunrise06]

Hence my question as it did appear to work in my test environment which is not the final setup for these files.

Since it did not work, do you know what may be wrong? I get a #REF! error which resolves once the lookup file is open. This is contrary to what you inidcate how it should work.

Thanks!

 

[Aladin Akyurek]

Hence my question as it did appear to work in my test environment which is not the final setup for these files.

Since it did not work, do you know what may be wrong? I get a #REF! error which resolves once the lookup file is open. This is contrary to what you inidcate how it should work.

Thanks!

I'm not familiar with the sharepoint environment. Maybe something in the formula itself causes the problem. Care to post it?

 

[sunrise06]

this is the formula:

=VLOOKUP(A1,'NTK Activity Master.xlsx'!activity[#Data],1,FALSE)

The lookup file is currently saved on a memory stick and the receiving file is saved on the C: drive.

Thanks

 

[Aladin Akyurek]

this is the formula:

=VLOOKUP(A1,'NTK Activity Master.xlsx'!activity[#Data],1,FALSE)

The lookup file is currently saved on a memory stick and the receiving file is saved on the C: drive.

Thanks

If VLOOKUP references an Excel Table (obtained with Insert | Table) in a closed book, a #REF! error surfaces up. Conclusion: Don't use the Table functionality, i.e., convert the table with Convert to Range to an ordinary look up table.

 

[sunrise06]

Interesting. I was referring to this suggestion which will be needed in my source data.

"Use Tables or Lists [Excel 2003 or above only]
Using named ranges or absolute references is good technique. But they suffer from one nagging limitation. If your source data (where you are looking up) grows or shrinks, you need to adjust the references. A better option? Use tables (or lists in 2003). "

At the office I have it setup with absolute reference and it works for existing entries but not if I add to the list. I was hoping the tables would resolve that issue.

Either way it's not working. UGH

 

[Aladin Akyurek]

Interesting. I was referring to this suggestion which will be needed in my source data.

"Use Tables or Lists [Excel 2003 or above only]
Using named ranges or absolute references is good technique. But they suffer from one nagging limitation. If your source data (where you are looking up) grows or shrinks, you need to adjust the references. A better option? Use tables (or lists in 2003). "

At the office I have it setup with absolute reference and it works for existing entries but not if I add to the list. I was hoping the tables would resolve that issue.

Either way it's not working. UGH

What is the range of this Excel able?