Looking up and reporting values across tabs

G

gareth.wretham

I have a spreadsheet with a list of codes on separate tabs, each tab
is named according to its category.

On the last tab I have a list containing of ALL the above codes, but
not which category the code is in.

I want a formula to look at all of the tabs (apart from the final one)
and return the category for each code and place it next to the
relevant code in the
list on the last tab.

The end result is that each code in the complete list will have the
correct category next to it.

Any help appreciated.
 
D

Dave Peterson

I think I'd create a new (temporary) worksheet that contained the part codes in
column A and the worksheet name for each code in column B.

If you want to try a macro, this worked ok for me:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim RptWks As Worksheet
Dim SummWks As Worksheet
Dim DestCell As Range
Dim RngToCopy As Range

Set SummWks = Worksheets("Summary") '<-- change this
Set RptWks = Worksheets.Add
With RptWks
.Range("a1").Resize(1, 2).Value = Array("Part#", "Category")
.Range("b:b").NumberFormat = "@" 'text
End With

For Each wks In ActiveWorkbook.Worksheets
Select Case wks.Name
Case Is = SummWks.Name, RptWks.Name
'do nothing to these sheets
Case Else
With RptWks
Set DestCell _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
With wks
'This copies the data in column A in each worksheet
'change this (twice) to the column that you use
Set RngToCopy _
= .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With
RngToCopy.Copy _
Destination:=DestCell
DestCell.Offset(0, 1).Resize(RngToCopy.Rows.Count, 1).Value _
= wks.Name
End Select
Next wks
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

After you have this extra worksheet created, you could use =vlookup() with just
one lookup range to return the category for each part number.
 
B

Barb Reinhardt

1 - Are the worksheet names the same as what you call "codes"?
2 - How does someone determine the category the code is in? It's not
intuitively obvious.
 
G

gareth.wretham

Thanks Dave, I had no idea it would be so involved so appreciate you
taking the time to do this.

Best regards
Gareth
 
Top