VLOOKUP on closed workbook table_array

[S7ewie]

I use VLOOKUP's quite a lot. I'm trying to put together a macro that I can use to lookup a value in a table on a closed workbook. For now though I'm just trying to get it to work as a formula so that I can work out how to reference the table array.

My current forumla is..

Please Login or Register  to view this content.

It works fine work the workbook open but whenever I close it, i just get #REF. Is there a way around this so that I can get it to work without needing to have both workbooks open?

[Glenn Kennedy]

VLOOKUP does not work with Tables on closed worksheets. Convert the Table to a range, or (if you want to keep the Table) use whole column references pointing to the source table.

[S7ewie]

Ahhh I see thanks!

I want to keep the whole table because the whole idea is that it will grow as people add to it. If i referance the exact range it'll miss things added beyond it.

How do I use whole column references?

EDIT: Nevermind just worked it out. Thanks

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[S7ewie]

Ok so bonus question time...

I've put it into VBA but now I'm having the same issue. Works fine with both workbooks open but error pops up when I close the workbook with the table array (I've more to add and tidy up but I want to get the basics working before I worry too much about that..

Please Login or Register  to view this content.

[Glenn Kennedy]

I know NOTHING about VBA!!! so, sorry....