Excel Workbook link won't update without open

Hi,
Currently I have a workbook that has formula that link to another workbook.
Problem is the formula result is not updated if not open the linked workbook.
How can I make it update without open the linked workbook?

I notice that if the linked formula is only a few of it, it will update even the linked workbook is not open.
But if there are many formula that link to another workbook, then it will not update.

Any advice?

It depends which formula you mean. VLOOKUP does not require the other workbook to be open, some other formulas do.

care to share the formula?

well for example this formula will be static if the linked workbook is not open.

Please Login or Register  to view this content.

is there anyway to make it work without open the linked workbook

It's not clear from your formula what you want to do. Please post a workbook and explain in context.

I have no problem using Index with Small on a closed workbook, but your use of ROWS($A$1:$A2) is, uhmm, kinda funny.

It's actually start from $A$1:$A1 but I copy it at the second row.

This is the dummy, Open Book1 only the result will never update

Works fine for me in Excel 2010. I created more data in Book2, saved and closed it. Then in Book1 I copied the formula down and it returned the correct result.

no no,
you see there is a drop down to choose in Book1
when I select another value from drop down, the list is not updated

None of the formulas in Book1 reference the cell with the drop-down.

That cell is, in fact, referenced in Book2. As long as Book2 is closed, there won't be any calculations happening in Book2, hence a change in Book1 will only affect Book2 once you open it and it recalculates.

To re-iterate: The problem is not that the Index formula does not work on a closed workbook (because it does!). The problem is, that a closed workbook won't re-calculate its cells and formulas while it's closed.

And that is 1.) understandable, and 2.) a completely different issue.

cheers

Erm so is there some way to make it update without open book2

No...........................