Can Excel ignore file links that do not yet exist?
 

I have a set of formulae of the type below:

=SUM('V:\DailyReports\Brasil\2006\[BRA-2006-01-31.xls]EquipDataCollation'!$D$5:$F$5)

I am transferring the generation of quarterly reports to others and wish to
make it as easy as possible for them to update data. Therefore I would like
to generate formulae of the form above relating to files that do not yet
exist. The filename format is standardised.

What I can't seem to get Excel to do is ignore formulae where the file it
refers to does not exist yet.

Dave

Can Excel ignore file links that do not yet exist?
 

Try this:
=IF(ISERROR(SUM('V:\DailyReports\Brasil\2006\[BRA-2006-01-31.xls]EquipDataCollation'!$D$5:$F$5)),"",SUM('V:\DailyRe ports\Brasil\2006\[BRA-2006-01-31.xls]EquipDataCollation'!$D$5:$F$5)).

When you hit enter, it will prompt you for the link. I hit escape, and it
seemed to work.

"DaveyC4S" wrote:

I have a set of formulae of the type below:

=SUM('V:\DailyReports\Brasil\2006\[BRA-2006-01-31.xls]EquipDataCollation'!$D$5:$F$5)

I am transferring the generation of quarterly reports to others and wish to
make it as easy as possible for them to update data. Therefore I would like
to generate formulae of the form above relating to files that do not yet
exist. The filename format is standardised.

What I can't seem to get Excel to do is ignore formulae where the file it
refers to does not exist yet.

Dave

Can Excel ignore file links that do not yet exist?
 

Jonathan

I tried this but my aim is to avoid numerous Update Links prompts which
would happen as I have several thousand link formulae of this type.

My interim solution is to use Find/Replace and replace = with #= which
effectively switches off the formulae until they are required when
Find/Replace is used to reverse the process.

At the moment I am not sure if there is a way of Excel seeing that a link
does not exist and not reacting with an Update Link prompt but merely leaving
the cell at a default value.

Thanks for your help
Dave

"Jonathan Cooper" wrote:

Try this:
=IF(ISERROR(SUM('V:\DailyReports\Brasil\2006\[BRA-2006-01-31.xls]EquipDataCollation'!$D$5:$F$5)),"",SUM('V:\DailyRe ports\Brasil\2006\[BRA-2006-01-31.xls]EquipDataCollation'!$D$5:$F$5)).

When you hit enter, it will prompt you for the link. I hit escape, and it
seemed to work.

"DaveyC4S" wrote:

I have a set of formulae of the type below:

=SUM('V:\DailyReports\Brasil\2006\[BRA-2006-01-31.xls]EquipDataCollation'!$D$5:$F$5)

I am transferring the generation of quarterly reports to others and wish to
make it as easy as possible for them to update data. Therefore I would like
to generate formulae of the form above relating to files that do not yet
exist. The filename format is standardised.

What I can't seem to get Excel to do is ignore formulae where the file it
refers to does not exist yet.

Dave