Working with vlookups

[Byron720]

How do I use the vlookup function (or else) to find data from 4 different
sheets? For example I need to extract information about a part # but the list
is 4 sheets long. The part #'s are numeric and alphanumeric.

The report is 4 sheets big full of inventory data (for example Part #=column
A and price=column B). I added a 5th sheet and I need to get inventory
information about any part # as soon as I type it in

 

[Gord Dibben]

Why can't you have all the data on one sheet?

Each sheet can have 65536 rows of data.

Do you have 262,144 rows of data spread over 4 sheets so you need a fifth sheet
to add more?


Gord Dibben MS Excel MVP

 

[smw226 via OfficeKB.com]

Hi Byron,

If I am being honest I would say that you should probably put your data into
Access but as people don't find it too helpfull when I say that, here is a
tiny bit of VBA which should solve your problems:


HTH

Simon

=======================================================================
Sub extendedLookup()

Dim wksht As Worksheet
Dim i As Double
Dim lkupValue

'Cell where the value you want to find is stored
lkupValue = Sheets("Sheet4").Range("A1").Value

For Each wksht In ActiveWorkbook.Worksheets

For i = 1 To 65535
If wksht.Name <> "Sheet4" Then 'Sheet your lookup value is on
If wksht.Range("a1").Offset(i, 0).Value = lkupValue Then
MsgBox "Value found. Price is" & wksht.Range("a1").Offset(i, 1).
Value
Exit Sub
End If
End If
Next i

Next wksht
End Sub

========================================================================

 

[Byron720]

Yes Gord, I have almost 4 sheets (262,144 rows) of data. I just use the fifth
sheet to obtain the data from the other 4. they all have part #'s and prices.

 

[Gord Dibben]

Check out Biff's methods and a sample workbook at this google search result.

http://tinyurl.com/2xveaq


Gord