How do I get a popup window on a VLOOKUP lookup failure?

[Mark F]

I am using the VLOOKUP function and want to get an error indication
if there is not an exact match for the first argument (lookup_value).

I can get an error indication each cell with a problem by using the
4th argument to VLOOKUP and using the value "FALSE".

How do I get a indication that there a problem in either a fixed
location in the spreadsheet or a popop window?

I am using Office 2003, but, if need be, I can move to Office 2010.
I am using Windows XP and cannot upgrade.

 

[Jim Cone]

Assumes your lookup formula is in cell H4...
Enter this formula in your "fixed location" cell: =IF(ISERROR(H4),"PROBLEM","")
'---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Special Print XL add-in: long columns printed side by side)





"Mark F" <[email protected]>
wrote in message
news:[email protected]...

 

[Mark F]

Assumes your lookup formula is in cell H4...
Enter this formula in your "fixed location" cell: =IF(ISERROR(H4),"PROBLEM","")
'---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(Special Print XL add-in: long columns printed side by side)

Doesn't do what I want: I want ANY error to show up in some fixed
location.

There will be 1000s of cells.

 

[Jim Cone]

=IF(ISERROR(AVERAGE(C5:H1000)),"PROBLEM","OK")
Note: the range being verified must contain at least one numeric value.
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)



"Mark F" <[email protected]>
wrote in message

 

[Gord Dibben]

You want a message box pop up if there is any error in any cell out of the
1000's of cells?

What if there are 1,329 errors?

Do you want a message pop up for each one or.........???


Gord Dibben MS Excel MVP

 

[Mark F]

You want a message box pop up if there is any error in any cell out of the
1000's of cells?

What if there are 1,329 errors?

Do you want a message pop up for each one or.........???

One time, but a count of the found errors would be better, so I
know have an idea of how many problems I fixed when I fixed one
cell.

 

[Gord Dibben]

One time, but a count of the found errors would be better, so I

know have an idea of how many problems I fixed when I fixed one
cell.

This may get you started but not sure what you mean about fixing one cell.

Sheet event code to count error cells after calculation takes place.

Private Sub Worksheet_Calculate()
Dim rng As Range
On Error GoTo endit
Application.EnableEvents = False
Set rng = Me.Range("A1:M100").SpecialCells(xlCellTypeFormulas, 16)
MsgBox rng.Count & " Errors found!!"
endit:
Application.EnableEvents = True
End Sub


Gord