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?
--
tb

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On 8/5/2014 at 10:21:36 AM Claus Busch wrote:

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.

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...

--
tb

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(xlCellTypeFormu las, 16).Select
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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 F2do your
edittingEnter without losing the others.


Gord


On Tue, 5 Aug 2014 16:13:12 +0000 (UTC), "tb"
wrote:

On 8/5/2014 at 10:21:36 AM Claus Busch wrote:

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.

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...

On 8/5/2014 at 11:46:48 AM Gord Dibben wrote:

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 F2do your
edittingEnter 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.
--
tb

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On 8/5/2014 at 2:31:52 PM Claus Busch wrote:

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.

Thanks Claus and Gord.

--
tb