Goal: Look up data from an external workbook (workbook A) and return data on different workbook (Workbook B).

My Proposed Solution: On workbook B, I have a row of accounts (row 1) and a column of things in the account (column A). In cell B2, I have a function:

'index((Workbook A, column E), match(B$1&$A2, (Workbook A, column B), 0))'

In column E on Workbook A is the value I want to return and Columb B on Workbook A is the concatenated account and things in account. The function looks up the concatenated version and returns the number of that specific thing in the account. It works beautifully when the two workbooks are open. I can add an account or things in the account, drag the formula over or down, and it returns the value of that specific thing in that specific account. I have defined the external columns (E and B in Workbook A) as different names, and I just use the names in the function. Still, works lovely.

The problem comes when I close the external workbook (workbook A) or open Workbook B without opening workbook A. Say I just open workbook B and leave workbook A closed....I press "update links" and get the error message "Excel cannot complete this task with available resources. Choose less data or close other applications." I press "ok" and all the cells which link to a number are now "#ref!", whereas all the cells that can't find the lookup return "#n/a". So the wierd thing is that it knows the links that are supposed to work because the #ref! pops up instead of "#n/a". However, I would like the actual number, not a #ref!. If I open up Workbook A, all the #ref! cells populate correctly with the correct looked-up value. The "#n/a" are correctly still there. But my solution only works when both workbooks are open.

I did this exact same thing 2 months ago and had no problems. I could add accounts with the other workbook closed and it would still find and return all the correct values. I never came across the error message above...nothing. I don't know why it is now not letting me do this.

I'm using excel 2003. If my problem is not clear (sorry it's wordy), please let me know. I'll try to clarify. Does anyone know what the problem is?

Thank you so much. I hope to eventually have about 50 different workbooks all linking to Workbook A doing the same thing but looking up different accounts. I can't do that until one works properly though.