vlookup over multiple sheets (same workbook) help pelase

[chally3]

Can anyone help with a formula please, I am really stuck on this one.
I am trying to write a vlookup over multiple sheets but cannot get it to work.


Heres the problem, every Friday we download an excel spreadsheet from our database of items to buy, these are for 4 buyers who have there own worksheet.

When I download the new spreadsheet I need the vlookup to look across the worksheets to see whom has this items..


Sheet 1 columns

Column A Column B
Item Qty reqd

Sheets 2,3,4 & 5 are named as the buyers initials below


Sheet 2 = MC
Sheet 3 = ELU
Sheet 4 = TJA
Sheet 5 = TH

All of these sheets have the same columns as below

Column A Column B Column C
Item Qty reqd Initials


What I would like the formula to do is look in sheet 1 at the ‘item’ (say cell a1), then check the other worksheets & copy across whose initials (from column C,) that relate to that item.

Can this be done,

Thank you

 

[venkat1926]

see my notes in my hand book -- venkat

But VLOOKUP is only using one lookup value? You can use this technique

=VLOOKUP(A1,INDIRECT("'"&INDEX(MySheets,MATCH(TRUE,COUNTIF(INDIRECT("'"&MySheets&"'!A1:A50"),A1)>0,0))&"'!A:B"),2,0)

Where a list of sheet names is named "MySheets" and it will allow a de facto
VLOOKUP over multiple sheets.
The latter formula needs to be entered with ctrl + shift & enter.

An example can be downloaded here

http://nwexcelsolutions.com/Download/3DVLOOKUP.xls

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com

the above is reply to the following

http://www.nwexcelsolutions.com/adv...r_formulas_that_can_do_this,_here_are_2_more:
"EXCELLRNR" <EXCELLRNR@discussions.microsoft.com> wrote in message
news:B582BECB-10DE-4FAD-8EBC-4AA41B11CEFE@microsoft.com...
> How can I create a lookup formula that looks at information in cell V9 and
> S9
> in sheet 1 and finds the information in one of 5 other sheets to return
> the
> correct value in cell K9 of sheet 1?

 

[Krishnakumar]

Hi,

If you prefer a VBA approach..

Sub test()
Dim i       As Byte
Dim lRow    As Long
Dim Rng     As Range
Dim ItemRng As Range
Dim fRow    As Long
Dim Itm     As Range
Dim ws      As Worksheet

Set ws = Sheets("Sheet1")
fRow = ws.Range("A" & Rows.Count).End(xlUp).Row
Set ItemRng = ws.Range("A2:A" & fRow)
Application.ScreenUpdating = False
For Each Itm In ItemRng
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> ws.Name Then
            Sheets(i).Activate
            lRow = Range("A" & Rows.Count).End(xlUp).Row
            Set Rng = Range("A1:C" & lRow)
                If Application.WorksheetFunction.CountIf(Columns(1), Itm) > 0 Then
                    With Rng
                        .AutoFilter field:=1, Criteria1:=Itm
                        .Offset(1, 2).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).Copy
                        ws.Cells(Itm.Row, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlValues, , , True
                        .AutoFilter
                    End With
                End If
        End If
    Next
Next
ws.Activate
Application.ScreenUpdating = True
End Sub

How to run:

1. Hit Alt+F11
2. Go to Insert > Module
3. Paste the code there
4. Close the window
5. Go to Tools > Macros > Macro and run the test

HTH