Lookup problems

I'm trying to make a model im working on more dynamic, by replacing all the simple external links with lookup and match statements, but this seems to be counter productive, as these statements dont seem to update like external links do (even clicking update values does bob all). What I mean is, all the lookup cells return 0's until the books the lookups are linked to are opened up.

Is there any basic solution to this problem, other than having a massive sheet of pasted links and performing the look ups internally within the workbook (we're talking 50+ workbooks and ~1000 worksheets!)? I'm thinking from reading the guide someone kindly had posted in their sig that this is probably this how excel expects things to be done, but I can't see it being practical for my use. It'll be a horrible/messy task, and take away all the 'dynamicness' im trying to create!

Ideas appreciated.

Originally Posted by broro183

hi,


Yes, you're right Excel is likely to behave this way, see the below links for some clarifications of Excel's limits:
http://decisionmodels.com/memlimitsc.htm
which states:





Wow, that's a lot of links & you may quite easily reach the above limit!

Unfortunately, I think the basic solution is a complete redesign of your model eg:
1) reverse the links by putting all the "variable inputs" into your model file & then having the multiple workbooks link into this file.
or
2) consolidate as many of the workbooks as possible into a single file so the model can just link to one file (or be the one file) & then use other tools such as Pivot Tables to allow for different displays of the data as required by the different end users.

I think the second option is the better one. Yes, it may* be a "horrible messy task" to complete but you will only need to do it once & it will allow for a much more maintainable/auditable model (ie "one version of the truth").

* depending on the workbook/sheet structures, somoeone on this site may be able to suggest a macro that will automate the consolidation of the individual files.

hth
Rob

I thought as much. I'm just not sure the archaic computers here would be able to open such a file. Not too keen on getting a good few weeks down the line and finding it wont open, so I'd need to do some tials.

I don't think there's any chance of getting under the external links limit to consolidate all the data to one workbook, so it'd have to be some kinda of marco to amalgomate the data rather than doing it through links.


I suppose if I could summarize each workbook onto one summary sheet per workbook, and then import those into 'final' workbook, it'll only be 50 worksheets and a couple of presentation sheets. Anyone have a gut feeling of whether 50-60 sheets of maybe 25000 cells would be a computer killer?