How do I scan a VLookup though multiple worksheets?

**rjmckay** · 05-23-2011, 02:21 PM

Thanks to a LARGE degree by Roger Govier. He helped me solve part one of my main project.

Now for the first time ever trying out VLOOKUP.... I think I choose a difficult application for it. LOL

I've attached both spreadsheets... The first one is the main database, the other is the "insert into here" spreadsheet (log example.xls).

I want it to return the number of tapes in cell D (whatever row) for today() in tape return tracker (into A10 of log example)
and number of tapes in cell B (whatever row) for today() in tape return tracker (into A11 of log example)

Seems simple enough, the trick is if this can be done across multiple worksheets within a whole workbook.

Thank you again for your help!

Once again, this is for Excel 2003

Edit: Cleaned up the tracker .... just updated newest copy of it

**davegugg** · 05-23-2011, 04:13 PM

I think the best way to accomplish this is to store all your data in tape return tracker.xls on one sheet. You can always use filter to find only records in a certain month.

**rjmckay** · 05-23-2011, 04:19 PM

Originally Posted by davegugg

I think the best way to accomplish this is to store all your data in tape return tracker.xls on one sheet. You can always use filter to find only records in a certain month.

Ok, sounds intriguing. I did consider putting everything on one page.... but this filter thing is new to me. Geesh, before attempting these projects I thought myself from intermediate to expert.... I'm going to have to lower my peg down a little to strictly intermediate! LOL

After I do that... I should be able to setup a VLOOKUP for today() in the other workbook referencing the now one sheet tracker?

**davegugg** · 05-23-2011, 04:48 PM

Yes, it should be no problem. You just include all the data in your table array argument.
It should also make it easier to modify the formula if you eventually add new data.

**rjmckay** · 05-24-2011, 01:20 PM

Originally Posted by davegugg

I think the best way to accomplish this is to store all your data in tape return tracker.xls on one sheet. You can always use filter to find only records in a certain month.

Ok..... been trying this...

The problem I'm having with this idea... is that I want this as fully automated as possible... so it can go year to year easily. This is why I broke it up into worksheets for each month.

Putting it all onto one worksheet creates the problem of dates figuring themselves out... on the top before I had the Year, and each month... Sure, every year I'd need to change the year... but everything else would figure itself out. Putting it into one list.... I can't figure out how to maintain this functionality. I've tried.. the problem I run into is that not every month has the same number of days, and I can't circular reference. LOL

**davegugg** · 05-24-2011, 02:26 PM

Well, you can use a macro to identify the sheet you will need to get the data from, then get the data.

Sub rjmckay()

    With Workbooks("logexample.xls").Sheets("Sheet1")
        Workbooks("tapereturntracker.xls").Activate
        Sheets(Format(.Cells(2, 1).Value, "mmmm")).Activate
        For i = 4 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
            If ActiveSheet.Cells(i, 1).Value = .Cells(2, 1).Value Then
                .Cells(10, 1).Value = ActiveSheet.Cells(i, 2).Value
                .Cells(10, 2).Value = ActiveSheet.Cells(3, 2).Value
                .Cells(11, 1).Value = ActiveSheet.Cells(i, 3).Value
                .Cells(11, 2).Value = ActiveSheet.Cells(3, 3).Value
                .Cells(12, 1).Value = ActiveSheet.Cells(i, 4).Value
                .Cells(12, 2).Value = ActiveSheet.Cells(3, 4).Value
                .Cells(13, 1).Value = ActiveSheet.Cells(i, 5).Value
                .Cells(13, 2).Value = ActiveSheet.Cells(3, 5).Value
            End If
        Next i
    End With
                
End Sub

I personally don't like this solution as well because it is very inflexible. If you choose to move anything on your data or "insert into" workbooks, you will have to alter the code. Hopefully, it will work well for you anyway.

**rjmckay** · 05-24-2011, 03:38 PM

Well I did find this.... a VBS to add an all sheets VLOOKUP

Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _
                       Col_num As Integer, Optional Range_look As Boolean)
   Dim wSheet As Worksheet
   Dim vFound
   On Error Resume Next
   For Each wSheet In Workbooks(Tble_Array.Parent.Parent.Name).Sheets
       With wSheet
           Set Tble_Array = .Range(Tble_Array.Address)
           vFound = WorksheetFunction.VLookup _
                    (Look_Value, Tble_Array, _
                     Col_num, Range_look)
       End With
       If Not IsEmpty(vFound) Then Exit For
   Next wSheet
   Set Tble_Array = Nothing
   VLOOKAllSheets = vFound
End Function

The problem I found with this... unless I'm doing it wrong, it seems to choke up when trying to gather info from a different workbook.

**rjmckay** · 05-25-2011, 01:00 PM

is there a better way to do this... I tried combining the two workbooks.... even tried putting them onto one sheet.... the issue I seem to run into is the today() function seems to flip out VLOOKUP...... Is there a better solution for this..... will Index/Match work? (something I just discovered, but not sure if it would work)

**NBVC** · 05-25-2011, 01:29 PM

You can possibly do it with INDEX/MATCH and also INDIRECT to pinpoint the correct worksheet... but, unfortunately, the INDIRECT function only works with open workbooks.

E.g.

=INDEX(INDIRECT("'[tape return tracker.xls]"&TEXT(TODAY(),"mmm")&"'!$D:$D"),MATCH(TODAY(),INDIRECT("'[tape return tracker.xls]"&TEXT(TODAY(),"mmm")&"'!$A:$A")))

and

=INDEX(INDIRECT("'[tape return tracker.xls]"&TEXT(TODAY(),"mmm")&"'!$B:$B"),MATCH(TODAY(),INDIRECT("'[tape return tracker.xls]"&TEXT(TODAY(),"mmm")&"'!$A:$A")))

Should work if both workbooks are open.

If you want to work with closed workbooks, you would need to download and install the Morefunc.dll addin form here and use INDIRECT.EXT

something like:

=INDEX(INDIRECT.EXT("'C:\YourPath\[tape return tracker.xls]"&TEXT(TODAY(),"mmm")&"'!$B:$B"),MATCH(TODAY(),INDIRECT.EXT("'C:\YourPath\[tape return tracker.xls]"&TEXT(TODAY(),"mmm")&"'!$A:$A")))