#N/A In Cells

[tb]

I am using Excel 2007 on a Windows 7 desktop.

I have this enormous main spreadsheet which is linked to many other
ones. Sometimes there are a few cells in the main spreadsheet with
"#N/A" in them.
Is there a formula that will quickly point out to me which cells of the
main spreadsheet have the "#N/A" in them?

 

[Claus Busch]

Hi,

Am Tue, 5 Aug 2014 15:13:14 +0000 (UTC) schrieb tb:

Is there a formula that will quickly point out to me which cells of the
main spreadsheet have the "#N/A" in them?

Start => Find & Select => Go to Special => Formulas => Errors
will select all formula cells with errors


Regards
Claus B.

 

[tb]

Hi,

Am Tue, 5 Aug 2014 15:13:14 +0000 (UTC) schrieb tb:


Start => Find & Select => Go to Special => Formulas => Errors
will select all formula cells with errors


Regards
Claus B.

Thanks, Claus.
Is there a quick way to jump from one highlighted "#N/A" cell to the
next one? Otherwise I have to go though the whole big spreadsheet many
times in order to find all the cells that are highlighted...

 

[Claus Busch]

Hi,

Am Tue, 5 Aug 2014 16:13:12 +0000 (UTC) schrieb tb:

Is there a quick way to jump from one highlighted "#N/A" cell to the
next one? Otherwise I have to go though the whole big spreadsheet many
times in order to find all the cells that are highlighted...

try it with Enter
But if you change a cell the highlighting is gone

You can also use a macro to highlight the cells
Sub Makro1()
ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 16).Select
End Sub


Regards
Claus B.

 

[Gord Dibben]

Select all cells with errors as Claus directed.

In found dialog use Shift and downarrow to select all "founds"

Close dialog. Founds will remain highlighted.

You can Tab through the found cells and edit each using F2>do your
editting>Enter without losing the others.


Gord

 

[tb]

Select all cells with errors as Claus directed.

In found dialog use Shift and downarrow to select all "founds"

Close dialog. Founds will remain highlighted.

You can Tab through the found cells and edit each using F2>do your
editting>Enter without losing the others.

Not sure I understand, Gord...

If I understand correctly your instructions I am supposed to:
1) Start => Find & Select => Go to Special => Formulas => Errors
2) Press Shift+Down Arrow in found dialog (?)
3) Close dialog
4) Tab through the highlighted cells

I don't get a found dialog anywhere when following the above-mentioned
instructions, so I am stuck at step 2.

 

[Claus Busch]

Hi,

Am Tue, 5 Aug 2014 18:45:39 +0000 (UTC) schrieb tb:

1) Start => Find & Select => Go to Special => Formulas => Errors
2) Press Shift+Down Arrow in found dialog (?)
3) Close dialog
4) Tab through the highlighted cells

I don't get a found dialog anywhere when following the above-mentioned
instructions, so I am stuck at step 2.

try Start => Find & Select => Find => What: #N/A => Options => Look At:
Values
But you can also edit your formulas with my first suggestion. You only
have to press F2 to edit.


Regards
Claus B.

 

[tb]

Hi,

Am Tue, 5 Aug 2014 18:45:39 +0000 (UTC) schrieb tb:


try Start => Find & Select => Find => What: #N/A => Options => Look
At: Values
But you can also edit your formulas with my first suggestion. You only
have to press F2 to edit.


Regards
Claus B.

Thanks Claus and Gord.