#VALUE! error in cells linked to other spreadsheets

[Andy Pilkington]

I have created a master spreadsheet with links to four other spreadsheets which shows the correct results when all linked files are open. However, if I open the master spreadsheet alone the linked cells show #VALUE ! rather than the value.

In case this is relevant the linked cells are a "SUMIF" formula and not simply a reference to one individual cell.

The number of linked spreadsheets will increase to 12 and the reason for the links is to avoid having to open all spreadsheets at one time. Please can anyone explain how I can eliminate this issue?

Thanks in anticipation.

Andy P

 

[oldbrewer]

in your master spreadsheet A1 put =[workbook2]sheet1!A1 and copy across and down
on sheet2 change to sheet2! etc etc

you now have all data in one workbook so put your formulas as helper cells on the sheets - maybe

 

[joeu2004]

Right: not all functions allow for references to external files that are closed.

You might replace the SUMIF with an equivalent SUMPRODUCT.

However, that will not be as efficient, especially if you use whole-column references like A:A instead of restricted ranges like A1:A10000.

(If you replace such SUMIF formulas, it would be prudent to use reasonable restricted ranges.)

Alternatively, you might wrap your SUMIF formulas with IFERROR that returns a default value. That allows your master file to make sense, albeit not reflecting the current values of the external files.

It depends on what your intent is.

 

[Andy Pilkington]

Thanks for this, I will give it a go.