Adding IF(ISERROR) to multiple and non-sequential cell references

[Lizabeta]

Is there an easy way to change

='Week 1'!$D$21
='Week 1'!$D$30
='Week 1'!$D$39
='Week 2'!$D$21
='Week 2'!$D$30
='Week 2'!$D$39

to

=IF(ISERROR('Week 1'!$D$21), "", ('Week 1'!$D$21))
=IF(ISERROR('Week 1'!$D$30), "", ('Week 1'!$D$30))
=IF(ISERROR('Week 1'!$D$39), "", ('Week 1'!$D$39))
=IF(ISERROR('Week 2'!$D$21), "", ('Week 2'!$D$21))
=IF(ISERROR('Week 2'!$D$30), "", ('Week 2'!$D$30))
=IF(ISERROR('Week 2'!$D$39), "", ('Week 2'!$D$39))

Without having to change each one by hand?

There is a pattern to the numbers.
There are 5 weeks, rows 12, 21, 30, 39, 48 in columns D,I,N,S,X

Thanks!
~Lizabeta

[pike]

The easiest way is to use the Edit > Find and select options Formula
quick and painless and can be undone if required

[shg]

As an alternative, LB, consider using conditional formatting to mask display of errors.

[DonkeyOte]

Alternative based purely on example file (assuming formulae in say A1 onwards):

Please Login or Register  to view this content.

Change ranges to suit...

[Lizabeta]

I tried each option in turn and found, surprisingly (since I'm not that experienced with VB) that DonkeyOte's solution was the one that worked best for me.

Shg, I am probably not using conditional formatting the way you meant. The only way I knew to supress those errors with CF was to make them white out. Which, when I go to average everything on that table will cause me issues later.

Thank you both!

[anandhn86]

Thanks, is there an excel addin that can do this if we select a range?

[Fotis1991]

Welcome to the forum.

This is an(almost) 5 years old thread. As per forum rules, you have to start your own thrad and if you like to provide a link to this one.

[zbor]

Correct.
I will close this thread now.