Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Lookup Across Multiple Sheets

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Lookup Across Multiple Sheets

    ALL:

    I'm having trouble looking up text from several worksheets...
    I've tried hlookup;

    Code:
    =HLOOKUP("H1",'1'!B6:O32,27,FALSE)
    but get nowhere when text is in different sheets.

    I've attached simplified sample... lookup "H1" - holiday1 on sheet1 and return date to "Holiday" sheet.

    I've tried vlookup across worksheets code, but can't figure out how to customize.

    Thanks in advance,
    Attached Files
    Last edited by Dave Hawley; November 23rd, 2006, 16:42.

  • #2
    Re: Hlookup Acrosos Multiple Sheets

    Try
    Code:
    Function HLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _
                              Row_num As Integer, Optional Range_look As Boolean)
    
    ''''''''''''''''''''''''''''''''''''''''''''''''
    'Written by OzGrid.com
    
    'Use HLOOKUP to Look across ALL Worksheets and stops _
    at the first match found.
    '''''''''''''''''''''''''''''''''''''''''''''''''
    Dim wSheet As Worksheet
    Dim vFound
    
    On Error Resume Next
    
        For Each wSheet In ActiveWorkbook.Worksheets
            With wSheet
            Set Tble_Array = .Range(Tble_Array.Address)
                vFound = WorksheetFunction.HLookup _
                (Look_Value, Tble_Array, _
                Row_num, Range_look)
            End With
            If Not IsEmpty(vFound) Then Exit For
        Next wSheet
    
        Set Tble_Array = Nothing
        HLOOKAllSheets = vFound
    End Function

    Comment


    • #3
      Re: Lookup Across Multiple Sheets

      Couple of notes.

      1. Read this article

      2. Redesign your layout

      3. Try to put all the data in one sheet

      4. Apply Data-Validation wherever possible.


      From here you can use powerful Excel utilities like Pivot Tables, Auto Filter, Advanced Filter etc.

      Also see the attachment.

      HTH
      Attached Files
      Last edited by Dave Hawley; November 23rd, 2006, 17:10.
      Kris

      ExcelFox

      Comment

      Trending

      Collapse

      There are no results that meet this criteria.

      Working...
      X