Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Lookup Across Multiple Sheets

  1. #1
    Join Date
    8th November 2006
    Posts
    4

    Lookup Across Multiple Sheets

    ALL:

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

    VB:
    =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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Dave Hawley; November 23rd, 2006 at 16:42.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

    Re: Hlookup Acrosos Multiple Sheets

    Try
    VB:
    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 
    
    

  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Dave Hawley; November 23rd, 2006 at 17:10.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Lookup Across Multiple Sheets In Different Book
    By Peinecone in forum Excel General
    Replies: 3
    Last Post: October 25th, 2007, 17:49
  2. Multiple Column Lookup Across Multiple Sheets
    By Miraclez in forum Excel General
    Replies: 27
    Last Post: April 13th, 2007, 01:10
  3. Dates Lookup Across Multiple Sheets
    By skye9 in forum Excel and/or Email Help
    Replies: 1
    Last Post: October 11th, 2006, 03:05
  4. Lookup value across multiple sheets
    By andrea83 in forum Excel General
    Replies: 3
    Last Post: January 1st, 2006, 05:55
  5. Lookup on multiple sheets
    By cuhsd in forum Excel General
    Replies: 5
    Last Post: December 16th, 2005, 17:19

Bookmarks

Posting Permissions

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