Advice needed on autocalculation

[ChrisP]

I have a spreadsheet that has a number of rows (@1500) and a number of
columns @70) most of the cells contain multiple VLOOKUPS (they have to look
up to 6 different workbooks). As you can imagine this is taking a while to
calculate.

I was thinking about turning off the autocalc have all the associated
workbooks open, update the links then close. But I'm not sure if when the
spreadsheet is allowed to calculate, if the end results will be correct.

Anyone have any thoughts on this?

Thanks,
Chris

 

[Spencer.Sadkin]

Why do you need to have the associated workbooks open? I doubt you
would have any issues, but cant you test it for values you know should
exist?

 

[ChrisP]

My problem is that one of the workbooks is password protected and i can't
give the password out. So I'm using VBA to update the links and I have the
password in the VBA (which I then password protect). I'm new to VBA and I was
told that the associated workbooks have to be opened to update the links if
doing it this way, is this not true? If I don't have to open them how would I
write the VBA to update the links w/o opening the workbooks?

Thanks,
Chris