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

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.

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

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"
wrote in message
...
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.

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

On Mon, 27 Jun 2011 18:18:41 -0700, "Jim Cone"
wrote:


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.





"Mark F"
wrote in message
...
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.

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

=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"
wrote in message
...
Doesn't do what I want: I want ANY error to show up in some fixed
location.

There will be 1000s of cells.
"Mark F"

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

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

On Wed, 29 Jun 2011 08:34:30 -0400, Mark F wrote:

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

There will be 1000s of cells.

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

On Wed, 29 Jun 2011 12:07:58 -0700, Gord Dibben
wrote:

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 MS Excel MVP

On Wed, 29 Jun 2011 08:34:30 -0400, Mark F wrote:

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

There will be 1000s of cells.

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

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(xlCellTypeFormula s, 16)
MsgBox rng.Count & " Errors found!!"
endit:
Application.EnableEvents = True
End Sub


Gord