Hi all,
in cells B1:B1000 I have a simple VLOOKUP formula
=IF(ISBLANK(A1),"",VLOOKUP(A1,$L$1:$M$36,2,TRUE))
that will give a result of either L,M or H depending on the value placed in
the corresponding range A1:A1000
is there any way to check the last 6 populted cells in the range B1000:B1
and if either of the letter L,M or H haven't been displayed have a Msg box
appear to state which one (either L,m or H)

eg

in cells B23:B27 are the values:

M
M
M
L
M
L

so a msg box would now say :

"H - hasn't shown in 6"

any ideas??

many thanks

Here is one way I came up with that works. It has a bit of setup involved
but once done would do the trick.
In column c put this formula all the way down: (shown is c2's formula):
=IF(B2="L",ROW(B2),IF(B2="M",ROW(B2),IF(B2="H",ROW(B2),"")))

Then in F2 place this formula: Max(C:C)
Then In Cell F4 place this formula:
=INDIRECT(ADDRESS(F2,2,1,1))
And in F5:
=INDIRECT(ADDRESS(F2-1,2,1,1))
And in F6:
=INDIRECT(ADDRESS(F2-2,2,1,1))
and in F7:
=INDIRECT(ADDRESS(F2-3,2,1,1))
And in F8:
=INDIRECT(ADDRESS(F2-4,2,1,1))
and in f9:
=INDIRECT(ADDRESS(F2-5,2,1,1))
Then in F10:
=IF(F9="L",1,IF(F8="L",1,IF(F7="L",1,IF(F6="L",1,IF(F5="L",1,IF(F4="L",1,0))))))
in F11:
=IF(F9="M",1,IF(F8="M",1,IF(F7="M",1,IF(F6="M",1,IF(F5="M",1,IF(F4="M",1,0))))))
and in F12:
=IF(F9="H",1,IF(F8="H",1,IF(F7="H",1,IF(F6="H",1,IF(F5="H",1,IF(F4="H",1,0))))))

Now create named ranges as follows:
LShown is F10
MShown is F11
and HShown is F12


Then Press Alt-F11 to go to the VBA editor, go to the ThisWorkbook and enter
this sub:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Application.Goto Reference:="LShown"
If ActiveCell.Value = 0 Then
MsgBox ("L Has Not Shown in 6")
End If
Application.Goto Reference:="MShown"
If ActiveCell.Value = 0 Then
MsgBox ("M Has Not Shown in 6")
End If
Application.Goto Reference:="HShown"
If ActiveCell.Value = 0 Then
MsgBox ("H Has Not Shown in 6")
End If

End Sub

That should do it. It has a bit of overhead, particularly column c.

Mike,
Many thanks for your help with that,
seems to do the trick - so cheers !

"Mike H." wrote:

> Here is one way I came up with that works. It has a bit of setup involved
> but once done would do the trick.
> In column c put this formula all the way down: (shown is c2's formula):
> =IF(B2="L",ROW(B2),IF(B2="M",ROW(B2),IF(B2="H",ROW(B2),"")))
>
> Then in F2 place this formula: Max(C:C)
> Then In Cell F4 place this formula:
> =INDIRECT(ADDRESS(F2,2,1,1))
> And in F5:
> =INDIRECT(ADDRESS(F2-1,2,1,1))
> And in F6:
> =INDIRECT(ADDRESS(F2-2,2,1,1))
> and in F7:
> =INDIRECT(ADDRESS(F2-3,2,1,1))
> And in F8:
> =INDIRECT(ADDRESS(F2-4,2,1,1))
> and in f9:
> =INDIRECT(ADDRESS(F2-5,2,1,1))
> Then in F10:
> =IF(F9="L",1,IF(F8="L",1,IF(F7="L",1,IF(F6="L",1,IF(F5="L",1,IF(F4="L",1,0))))))
> in F11:
> =IF(F9="M",1,IF(F8="M",1,IF(F7="M",1,IF(F6="M",1,IF(F5="M",1,IF(F4="M",1,0))))))
> and in F12:
> =IF(F9="H",1,IF(F8="H",1,IF(F7="H",1,IF(F6="H",1,IF(F5="H",1,IF(F4="H",1,0))))))
>
> Now create named ranges as follows:
> LShown is F10
> MShown is F11
> and HShown is F12
>
>
> Then Press Alt-F11 to go to the VBA editor, go to the ThisWorkbook and enter
> this sub:
>
> Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
>
> Application.Goto Reference:="LShown"
> If ActiveCell.Value = 0 Then
> MsgBox ("L Has Not Shown in 6")
> End If
> Application.Goto Reference:="MShown"
> If ActiveCell.Value = 0 Then
> MsgBox ("M Has Not Shown in 6")
> End If
> Application.Goto Reference:="HShown"
> If ActiveCell.Value = 0 Then
> MsgBox ("H Has Not Shown in 6")
> End If
>
> End Sub
>
> That should do it. It has a bit of overhead, particularly column c.