How can I clear all cells containing #n/a on a spreadsheet?

[Guest]

I use a lookup function in excel to pull data for selected users. When the
user is not listed in the range, the result is #n/a.

Rather than selecting each #n/a cell and clearing contents, I was wondering
if there is a function, formula or macro I can use that will just clear all
cells returning #n/a value at one time.

It would save me TONS of time every morning...

 

[Guest]

Edit > Goto > Special > Formula > Errors
and then clear all of them in one fell swoop!!

 

[Robert]

I use a lookup function in excel to pull data for selected users. When the
user is not listed in the range, the result is #n/a.

Rather than selecting each #n/a cell and clearing contents, I was wondering
if there is a function, formula or macro I can use that will just clear all
cells returning #n/a value at one time.

It would save me TONS of time every morning...

You could also use the ISNA function to check the result of vlookup
before displaying it, it effectively doubles the number of lookups so
it isn't too efficient though.

 

[Guest]

Could you elaborate on how I would incorporate the ISNA function into the
formula? I've never used this fuction before...

Thanks,

 

[Gord Dibben]

Why do you want the clear the cells with #N/A ?

For display purposes?

It screws up your calculations downstream?

To add the ISNA function to a VLOOKUP formula and hide the error...........

=IF(ISNA(VLOOKUP(G5,$C$1:$F$24,2,FALSE)),"",VLOOKUP(G5,$C$1:$F$24,2,FALSE))

This will make the cell look blank and allow calculations.


Gord Dibben MS Excel MVP