Linked Excel files: problems updating, #VALUE! data type errors 2

[TomYC]

I am looking for some "rules of thumb" for opening and updating linked Excel files. I have numerous files so connected, with formulas such as COUNTIF, SUMPRODUCT, INDEX, MATCH, OFFSET, etc. involved. I'm in the habit of opening all these files together, as that usually facilitates the process, better than, say, running Data, Edit Links, Update Values on closed files.
However I am now, in Excel 2010, frequently in the situation where one or more of my linked spreadsheets appears with certain worksheets completely filled with #VALUE! errors (wrong data type), even though I know that, under the right conditions, all these values/calculations will in fact display correctly. Right now I'm in a certain situation where I can try opening files A & B together, or first A and then B or first B and then A and nothing is working. Mind you, nothing in these formulas has changed at all! I know I will get it eventually, and it may merely be my computer running out of memory (I restart several times a day), but I would like to come up with a plan.
Any thoughts? Similar experiences?
T.Y.

 

[SkipVought]

hi,

IMNSHO, linking workbooks is a minefield nightmare that I avoid.

Short of that, make sure that Calculation is set to Automatic. If its already Automatic, then try setting Calculation to Manual before opening any subsequent workbook. I think its a crapshoot

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TomYC]

Thanks, Skip; I'll try the latter and take the former under advisement!
But I would say that what is any accounting system, if not a set of linked spreadsheet files of some sort?
If you're saying Excel is not quite the tool for this, well, then, you may just be correct--
T.Y.

 

[Andrzejek]

what is any accounting system, if not a set of linked spreadsheet files of some sort?

I would say any (decent) accounting system is a set of linked relational tables in a relational data base of some sort.

Have fun.

---- Andy

 

[SkipVought]

I wouldn't use workbook links.

I would query the other workbooks to get the data for analysis, a link of another sort.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[TomYC]

MS Query, then; I'll have to brush up!
Thanks