VLookups, External Links, Excel 2010 Tables giving #REF1 when updated and the source book closed

VLookups, External Links, Excel 2010 Tables...

Didn't find your answer?

Hi

Bit of an odd one. Don't normally use Linked Spreadsheets. What I have is a formula

=IF(ISNA(VLOOKUP([Payment Date],'I:\Support\Projects\Due Date Workaround Calendar.xlsb'!CalendarTable[#Data],1,FALSE)),[Payment Date], VLOOKUP([Payment Date],'I:\Support\Projects\Due Date Workaround Calendar.xlsb'!CalendarTable[#Data],2,FALSE))

This works fine when the source book is open but when it's closed I just get #REF!

Due Date Workaround Calendar.xlsb contains a Table called CalendarTable and I like the Table notation for formulas because they're:

1. Readable - I understand [Payment Date] from its name

2. Maintainable - [Payment Date] will always be returned no matter if you insert new columns into the table

3. Really cool - I'm a little geeky

Am I missing something regarding External Links?

Oh and when I get this done I can remove the last hurdle - the column number from vlookup

Thanks

Rich

Replies (1)

Please login or register to join the discussion.

avatar
By gitom
26th Apr 2013 13:48

Excel is doing what it does...

Nope, you aren't missing anything with regards to external links.  As far as I am aware, any Excel function which takes into account a range of cells (e.g.: VLOOKUP, HLOOKUP, SUMIF, etc.) will require the source file to be open for the function to work.  I don't think it matters whether or not the source data is formatted as a table or not.  The same does not apply to functions which reference once particular cell in the source file directly - they do not need the source file to be open.

Did you know that in more recent versions of Excel (not sure from which one) you don’t need to embed an ISNA function inside an IF?  There is an IFNA function which will evaluate a statement (in the same way as IF does) and return the result of the statement if it is true, otherwise will return some other value.  Same result, just a shorter (and easier to read) formula.  I note you are returning a different value to the one you are looking up, but it might work in your case:

=IFNA(VLOOKUP([Payment Date],'I:\Support\Projects\Due Date Workaround Calendar.xlsb'!CalendarTable[#Data],2,FALSE),[Payment Date])

Thanks (0)