Analysis of data

[Guest]

Hello

I have 52 sheets in the same workbook.
Sheet 51 is a summary sheet, sheets 1 - 50 are detail sheets.
I would like sheet 52 to be an analysis sheet.
In the top corner of sheet 52 I would like to be able to put a code
reference on and below it, it then lists all of the sheets that contain that
code and what value the codes has next to it.

What is the easiest way of doing this?

Thanks

 

[vezerid]

Dan
the following macro will read a code in A1 of a sheet named Analysis.
It will then clear the area below and produce a 2-col output, sheet
name and value next to the code. It assumes that your detail sheets are
before summary and analysis. It also assumes that a code appears max
once per sheet.

Sub Analysis()
Dim codecell As Range
nextrow = 2
cod = Sheets("Analysis").Range("A1").Value
Sheets("Analysis").Range("A2:B1000").ClearContents
For i = 1 To 50
Set codecell = Sheets(i).Cells.Find(cod)
If Not codecell Is Nothing Then
Sheets("Analysis").Cells(nextrow, 1) = Sheets(i).Name
Sheets("Analysis").Cells(nextrow, 2) = codecell.Offset(0, 1)
nextrow = nextrow + 1
End If
Next i
End Sub

You can further automate the process by using an event procedure for
your Analysis worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then Call Analysis
End Sub

With this, each time a code is changed in Analysis!A1, the macro will
run automatically.

Does this help?
Kostis Vezerides