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

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

On Mon, 28 Jan 2008 09:21:01 -0800, Byron720
wrote:

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

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 wrote:
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

--
--------------------
Simon - UK

Email at simon22mports [ a t ] hot mail [ d ot ]com

Message posted via http://www.officekb.com

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.
on the fifth page I just need to get the price as soon as I type the part #.

"Gord Dibben" wrote:

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

On Mon, 28 Jan 2008 09:21:01 -0800, Byron720
wrote:

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

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

http://tinyurl.com/2xveaq


Gord

On Mon, 28 Jan 2008 11:56:01 -0800, Byron720
wrote:

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.
on the fifth page I just need to get the price as soon as I type the part #.

"Gord Dibben" wrote:

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

On Mon, 28 Jan 2008 09:21:01 -0800, Byron720
wrote:

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