PDA

View Full Version : Solved: Check Date From A List



U_Shrestha
04-24-2008, 01:53 PM
Hi,

I have attached a sample sheet for my problem. Basically I want to see if there are ANY INSTANCE of post 86 Underground tanks in a list when I filter table for a particular station number. Thanks.

Bob Phillips
04-24-2008, 03:09 PM
Put this in D2 and copy down


=IF(C2="","No Data",IF(AND(B2="Underground",YEAR(C2)<1987),"Yes","No"))

and then put this in G1


=SUMPRODUCT(SUBTOTAL(3,OFFSET(D1,ROW($D$2:$D$18)-ROW($D$1),,1)),--($D$2:$D$18="Yes"))>0

U_Shrestha
04-24-2008, 06:14 PM
hello xld,

thanks for the formula. the tricky part is there are two values with the underground, i) underground ii) underground-def. i want the formula to count both the words alike.

the formula result in "No" for "Underground-def":

IF(C2="","No Data",IF(AND(B2="Underground",YEAR(C2)<1987),"Yes","No"))

maybe we need to insert a wildcard feature inside the formula, something like "Underground*", but that didn't literally work for me.

Bob Phillips
04-25-2008, 12:08 AM
If it will always start with the text use


=IF(C2="","No Data",IF(AND(LEFT(B2,11)="Underground",YEAR(C2)<1987),"Yes","No"))

If it could be anywhere within use


=IF(C2="","No Data",IF(AND(ISNUMBER(SEARCH("Underground",B2)),YEAR(C2)<1987),"Yes","No"))

tstav
04-25-2008, 04:04 AM
Another option, using VBA.

Formulas in column D have been replaced by 2 events (Worksheet_SelectionChange and Worksheet_Change) which I use to fill new values in column D (Yes,No,AST and whatever else you may desire) whenever you change values in Col B or C.

The G1 value is calculated by the Worksheet_Calculate event, every time you apply a filter.
Important: Add a "fake" formula (doesn't really do anything) to cell H1 to cause a recalculation every time you apply a filter.

=IF(H2="","","")
This recaculation is needed for the _Calculate event to fire.

Cell G1 checks for any "Yes" in column D and is either filled with "Yes" or emptied.

Don't forget to declare the prevCell variable before all code. This variable traps the activecell location (in the _SelectionChange event) and is used effectively in the _Change event.

A lot more than applying a handful of formulas, I know, but here it is, just in case...


Option Explicit
Dim prevCell As Range

Private Sub Worksheet_Calculate()
' Check column D for a "Yes" value
Dim cel As Range, rng As Range
On Error GoTo ErrorHandler
Application.EnableEvents = False
' clear cell
Range("G1").Value = ""
' set the range of column D values
If ActiveSheet.AutoFilterMode = False Then
' set range of all "Post '86" cells
With Range("A1").CurrentRegion
Set rng = .Offset(1, 3).Resize(.Rows.Count - 1, 1)
End With
Else
' set range of visible "Post '86" cells
With ActiveSheet.AutoFilter.Range
Set rng = .Offset(1, 3).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
End With
End If
' Check for "Yes"
For Each cel In rng
If cel.Value = "Yes" Then
Range("G1").Value = "Yes"
Exit For
End If
Next
ErrorHandler:
Application.EnableEvents = True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
' Update the D column value in case of changes in B, C columns
Dim rng As Range
With Range("A1").CurrentRegion
Set rng = .Offset(1, 1).Resize(.Rows.Count - 1, 2)
End With
If Not Intersect(prevCell, rng) Is Nothing Then
On Error GoTo ErrorHandler
Application.EnableEvents = False
With prevCell
If Cells(.Row, "B").Value = "" Then
Cells(.Row, "D").Value = "No Type"
ElseIf Cells(.Row, "C").Value = "" Then
Cells(.Row, "D").Value = "No date"
ElseIf Cells(.Row, "B").Value Like "Underground*" Then
If Cells(.Row, "C").Value < DateSerial(1986, 12, 31) Then
Cells(.Row, "D").Value = "Yes"
Else
Cells(.Row, "D").Value = "No"
End If
ElseIf Cells(.Row, "B").Value Like "Aboveground*" Then
Cells(.Row, "D").Value = "AST"
Else
Cells(.Row, "D").Value = "???"
End If
End With
ErrorHandler:
Application.EnableEvents = True
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Store active cell if within columns B or C
If Not Intersect(Target, Columns("B:C")) Is Nothing Then
Set prevCell = Target
End If
End Sub

tstav
04-25-2008, 07:19 AM
In case you decide to use the above VBA, you'll want to initialize the col D values with Yes, No, AST right from the start (to replace the formulas you have there currently).
This will do just that. Put it in the general code module and run it once. In case your data is filtered, the code will ShowAllData, just so that you can watch the values changing (stepping through it).


Private Sub InitializeColumnD()
' Initialize column D with values Yes, No, AST
' depending on values in columns B and C
Dim cel As Range, rng As Range
On Error GoTo ErrorHandler
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
With ActiveSheet
If .FilterMode = True Then
.ShowAllData
End If
End With
With Range("A1").CurrentRegion
Set rng = .Offset(1, 3).Resize(.Rows.Count - 1, 1)
End With
For Each cel In rng
If cel.Offset(0, -2).Value = "" Then
cel.Value = "No Type"
ElseIf cel.Offset(0, -1).Value = "" Then
cel.Value = "No date"
ElseIf cel.Offset(0, -2).Value Like "Underground*" Then
If cel.Offset(0, -1).Value < DateSerial(1986, 12, 31) Then
cel.Value = "Yes"
Else
cel.Value = "No"
End If
ElseIf cel.Offset(0, -2).Value Like "Aboveground*" Then
cel.Value = "AST"
Else
cel.Value = "???"
End If
Next
ErrorHandler:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

U_Shrestha
04-29-2008, 05:56 AM
thanks xld for your formula and tstav for your codes. for now i will use the xld's formula and keep the code in my collection :)

sorry for the late response.

Bob Phillips
04-29-2008, 06:14 AM
thanks xld for your formula and tstav for your codes. for now i will use the xld's formula and keep the code in my collection :)

sorry for the late response.

Good call!