Turning off "invalid ref" messages

[Bronwyn]

Got a quirky one for you. I have a user that uses her spreadsheet in an
unusual way. She has several columns that resove to #ref. Based on whether
or not the column is in #ref status determines if it is calculated or not.
The downside of this is that Excel 2007 keeps coming up with the error
message saying that "your workbook contains several invalid references". We
have tried turning off all the error checking in Excel Options > Formulas >
Error checking rules but it keeps coming up with the message.

How can we stop this message coming up?

 

[Shane Devenshire]

Hi,

What are the formulas that are resolving to #ref? This error usually occurs
because a formula references and location which is not valid, since once
these return REF they will never resolve to anything else in most cases, why
not replace the REF error with something else. Without more detail that is
really the best I can do.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Bronwyn]

The formula is:

=GETPIVOTDATA('[file location&name]PT'!$A$91,$G361&" "&$C361&" "&AY$5)

The issue is that this formula is in hundreds of cells and so they don't
want to go through and change all of them. They just want to turn off the
error message popping up.

Sorry it's taken so long to respond. Was on a training course all week.