Multiple sheet sum question

[ogsl]

Hello,

I have a large, mulit-sheet document with a summary sheet. Essentially it's sales figures for multiple employees over a number of years. Each sheet represents one year, organized by salesperson (in rows) and months (in columns).

The summary sheet has exactly the same format, but obviously collates all the data from all other sheets. I'm using the =SUM('2000:2009'!A1) type sum, which is working very well in this situation.

My question is: if I wanted to find out where that specific value actually comes from (i.e. from which sheet), how would I be able to do that? It's not too bad when you just have a couple of sheets, but the document I have has over 50 so rather then flick through all of them it would be handy to be able to see the origin of the value.

Thanks in advance for the help!

 

[Jonmo1]

Sounds like you should have 1 formula for each sheet on the summary.

On summary sheet,
have in say
A1 =2000!A1
A2 =2001!A1
A3 =2002!A1
etc..

Then sum those.

 

[ravishankar]

Hi
Paste the following codes in the macro window ( Alt F11). run the macro

Sub ogsl()
Dim a As Long
Dim b As String
b = InputBox("enter cell reference")
For a = 1 To Sheets.Count
If Worksheets(a).Name <> "Summary" Then
Worksheets("Summary").Cells(a, 1) = Worksheets(a).Name
Worksheets("Summary").Cells(a, 2) = Worksheets(a).Range(b)
End If
Next a
MsgBox "Complete"
End Sub

Enter the cell reference(A1,B5 etc). it lists the value from all sheets.
ravi