Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check column contents for series
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check column contents for series
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,I F(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,I F(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,I F(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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check column contents for series
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,I F(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,I F(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,I F(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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to check previous entries in column for same contents | Excel Worksheet Functions | |||
how to plot column chart with one series against multiple series. | Charts and Charting in Excel | |||
Check for Data series | Excel Programming | |||
Series of check boxes | Excel Programming | |||
how do I change a line series to a column series in excel? | Charts and Charting in Excel |