Results disappear when match is no longer found.

EBoudreau

Board Regular
Joined
Aug 21, 2015
Messages
143
I have the following formulas in 24 different cells in my "Stage Times" sheet looking up certain criteria in my "Frac Report" sheet. They are correctly entering the matches they find in the "Frac Report" tab but then when the criteria is no longer found, the matching result disappears in the cells where the formulas are.
Is there a way to code that instead of using formula's so the information doesn't dissappear if "LS" or "LSC" is removed, but hold the matching results in the cell until "LS" or "LSC" appears somewhere else in the range being looked at?


The formulas in their respective cells on the Stage Times Sheet as follows:
Excel Formula:
Cell I2 =IFERROR(@IF(COUNTIF('Frac Report'!F9:DB9,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F9:DB9,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F9:DB9,0))),"")
Cell S2 =IFERROR(@IF(COUNTIF('Frac Report'!F11:DB11,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F11:DB11,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F11:DB11,0))),"")
Cell AC2 =IFERROR(@IF(COUNTIF('Frac Report'!F13:DB13,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F13:DB13,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F13:DB13,0))),"")
Cell AM2 =IFERROR(@IF(COUNTIF('Frac Report'!F15:DB15,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F15:DB15,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F15:DB15,0))),"")
Cell I107 =IFERROR(@IF(COUNTIF('Frac Report'!F17:DB17,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F17:DB17,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F17:DB17,0))),"")
Cell S107 =IFERROR(@IF(COUNTIF('Frac Report'!F19:DB19,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F19:DB19,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F19:DB19,0))),"")
Cell AC107 =IFERROR(@IF(COUNTIF('Frac Report'!F21:DB21,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F21:DB21,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F21:DB21,0))),"")
Cell AM107 =IFERROR(@IF(COUNTIF('Frac Report'!F23:DB23,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F23:DB23,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F23:DB23,0))),"")
Cell I212 =IFERROR(@IF(COUNTIF('Frac Report'!F25:DB25,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F25:DB25,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F25:DB25,0))),"")
Cell S212 =IFERROR(@IF(COUNTIF('Frac Report'!F27:DB27,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F27:DB27,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F27:DB27,0))),"")
Cell AC212 =IFERROR(@IF(COUNTIF('Frac Report'!F29:DB29,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F29:DB29,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F29:DB29,0))),"")
Cell AM212 =IFERROR(@IF(COUNTIF('Frac Report'!F31:DB31,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F31:DB31,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F31:DB31,0))),"")
Cell I317 =IFERROR(@IF(COUNTIF('Frac Report'!F33:DB33,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F33:DB33,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F33:DB33,0))),"")
Cell S317 =IFERROR(@IF(COUNTIF('Frac Report'!F35:DB35,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F35:DB35,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F35:DB35,0))),"")
Cell AC317 =IFERROR(@IF(COUNTIF('Frac Report'!F37:DB37,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F37:DB37,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F37:DB37,0))),"")
Cell AM317 =IFERROR(@IF(COUNTIF('Frac Report'!F39:DB39,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F39:DB39,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F39:DB39,0))),"")
Cell I422 =IFERROR(@IF(COUNTIF('Frac Report'!F41:DB41,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F41:DB41,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F41:DB41,0))),"")
Cell S422 =IFERROR(@IF(COUNTIF('Frac Report'!F43:DB43,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F43:DB43,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F43:DB43,0))),"")
Cell AC422 =IFERROR(@IF(COUNTIF('Frac Report'!F45:DB45,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F45:DB45,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F45:DB45,0))),"")
Cell AM422 =IFERROR(@IF(COUNTIF('Frac Report'!F47:DB47,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F47:DB47,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F47:DB47,0))),"")
Cell I527 =IFERROR(@IF(COUNTIF('Frac Report'!F49:DB49,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F49:DB49,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F49:DB49,0))),"")
Cell S527 =IFERROR(@IF(COUNTIF('Frac Report'!F51:DB51,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F51:DB51,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F51:DB51,0))),"")
Cell AC527 =IFERROR(@IF(COUNTIF('Frac Report'!F53:DB53,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F53:DB53,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F53:DB53,0))),"")
Cell AM527 =IFERROR(@IF(COUNTIF('Frac Report'!F55:DB55,"LS")>0,INDEX('Frac Report'!F7:DB7,,MATCH("LS",'Frac Report'!F55:DB55,0)),INDEX('Frac Report'!F7:DB7,,MATCH("LSC",'Frac Report'!F55:DB55,0))),"")

For example in the screenshot below, Where the letter F is in place of what would be LS or LSC there, The number 47 stops showing up in the cell i want it to show in the stage times sheet.

Code i already have in the sheet is here for reference if it needs to be modified.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim xHour As String
    Dim xMinute As String
    Dim xWord As String
    Dim rngMonitored As Range, Rng As Range
    
    Set rngMonitored = Intersect(Target, Range("B4:C103,E4:F103,L4:M103,O4:P103,V4:W103,Y4:Z103,AF4:AG103,AI4:AJ103,B108:C207,E108:F1207,L108:M1207,O108:P207,V108:W207,Y108:Z207,AF108:AG207,AI108:AJ207,B212:C311,E212:F311,L212:M311,O212:P311,V212:W311,Y212:Z311,AF212:AG311,AI212:AJ311"))
    If rngMonitored Is Nothing Then Exit Sub
    
    Application.EnableEvents = False
    
    For Each Rng In rngMonitored
        xWord = Format(Rng.Value, "0000")
        xHour = Left(xWord, 2)
        xMinute = Right(xWord, 2)
        On Error Resume Next
        Rng.Value = TimeValue(xHour & ":" & xMinute)
    Next Rng
    
'    ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        True, AllowFormattingColumns:=Fasle, AllowFormattingRows:=True, Password:="WellingtonFrac"
    Application.EnableEvents = True
    

End Sub

I'm not sure if it can be done, but wanted to if anyone has any ideas.
 

Attachments

  • Match formulas.PNG
    Match formulas.PNG
    130.1 KB · Views: 2
Last edited:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Forum statistics

Threads
1,148,140
Messages
5,745,028
Members
423,916
Latest member
Bhavik123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top