Consulting

Results 1 to 8 of 8

Thread: Formula to FIND specific TEXT within a worksheet

  1. #1
    VBAX Contributor
    Joined
    Oct 2013
    Posts
    170
    Location

    Formula to FIND specific TEXT within a worksheet

    Is there a formula that will find specific text (Employee ID) in a worksheet and offset one row down and return the value in the offset cell to another worksheet? I am able to do it with a macro but I think a formula would be easier if it would work.

    Thank you for all your help
    I use Excel 2007

  2. #2
    VBAX Regular
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    60
    Location
    Research HLookup.
    How to attach file: How to upload your attachments (vbaexpress.com) To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,701
    Let's have a look at your code and/or attach a representative workbook…
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Contributor
    Joined
    Oct 2013
    Posts
    170
    Location
    Below is the code that does work but I would like a formula (if possible) to find specific text and offset one row and return the value in the offset cell, is that possible?

    Thank you for your help

    Option ExplicitSub FindAllOnBoard()
        'Defining the variables.
        Dim GCell, GCell1, GCell2, GCell3, GCell4, GCell5, GCell6, GCell7 As Range
        Dim Page$, Txt$, Txt1, Txt2, Txt3, Txt4, Txt5, Txt6, Txt7, MyPath$, MyWB$, MySheet$
        Dim DestSh As Worksheet
        
        ThisWorkbook.Sheets("Lookup").Range("B42").ClearContents
        
        Set DestSh = ThisWorkbook.Worksheets("Lookup")
    
    
        'The text for which to search.
        Txt = "Employee ID"
        
        'Turn off screen updating, and then open the target workbook.
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
    
    
        'Search for the specified text
        Set GCell = Sheets("CognosIntermData").Cells.Find(Txt, LookIn:=xlValues, LookAt:=xlWhole) '.Cells.Find(Txt) 'ActiveSheet
    
    
        'Record the address of the data, along with the date, in the current workbook.
        'With ThisWorkbook.Sheets("Lookup Data").Range("B1")
        
        ThisWorkbook.Sheets("CognosIntermData").Activate
        
        If GCell <> "" Then
        DestSh.Range("B42").Value = GCell.Offset(1, 0)
      
           End If
        
       End Sub

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,701
    …from which I glean you want to find a value within an entire sheet, whichever column and row it's in.
    Will the sought text appear more than once in the sheet? If so which one should we take (eg. it's found in both cells C2 and B3; which one to take?)
    Are we only to use the built-in functions in the formula? If so which version of Excel are you using? Does it have any of the dynamic functions such as FILTER, UNIQUE?
    Would it be OK to create a User defined function in a macro that might take the form of a formula on a sheet such as?:
    =FindInRange("Employee ID",CognosIntermData!A1:T1000)
    Bedtime here.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    VBAX Contributor
    Joined
    Oct 2013
    Posts
    170
    Location
    p45cal,

    Thank you for responding, I have come up with a formula (=ADDRESS(MAX(ROW(CognosIntermData!1:100)*(IF(CognosIntermData!1:100=B37,1, 0))),MAX(COLUMN(CognosIntermData!$1:$100)*IF(CognosIntermData!1:100=B37,1,0 )))) that finds the location where the text is but I am unfamiliar with ADDRESS and how to use the returned address/cell location and get it to OFFSET one row to get the value required.

    The sought text only appears once on the worksheet. If I can not get a formula to return the OFFSET value, I need a user defined function to complete the task if you have time to let me know how to complete that task.

    Thank you for any and all help

  7. #7
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,701
    Don't bother with ADDRESS; using the same idea try:
    =INDEX(CognosIntermData!1:100,MAX(ROW(CognosIntermData!1:100)*(IF(CognosIntermData!1:100=B37,1, 0)))+1,MAX(COLUMN(CognosIntermData!$1:$100)*IF(CognosIntermData!1:100=B37,1,0 )))
    Alternatively a user-defined function:
    Function FindInArray(Sought, myRange)
    Set GCell = myRange.Find(Sought, LookIn:=xlValues, LookAt:=xlWhole)
    If GCell Is Nothing Then FindInArray = "Not found" Else FindInArray = GCell.Offset(1).Value
    End Function
    The equivalent to the above using this UDF is:
    =FindInArray(B37,CognosIntermData!1:100)
    But please, answer my questions, again: which version of Excel are you using? Does it have any of the dynamic functions such as FILTER, UNIQUE?
    An extra question: do you have the LET function available to you on the sheet?
    Knowing the answer to this could considerably shorten and make more efficient the first formula.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #8
    VBAX Contributor
    Joined
    Oct 2013
    Posts
    170
    Location
    p45cal,

    Your suggestions worked well!

    Thank you for all your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •