Try this small User Defined Function:
Public Function FindcaseNumber(r As Range) As String
Dim s As Worksheet, v As Variant, ret As String, ffirst As Boolean
ret = ""
v = r.Value
addy = Application.Caller.Address
ffirst = True
For Each s In ThisWorkbook.Sheets
For Each rr In s.UsedRange
If rr.Address < addy Then
If rr.Value = v Then
If ffirst Then
ret = s.Name & rr.Address
ffirst = False
Else
ret = ret & ", " & s.Name & rr.Address
End If
End If
End If
Next
Next
FindcaseNumber = ret
End Function
UDFs are very easy to install and use:
1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window
If you save the workbook, the UDF will be saved with it.
To use the UDF from the normal Excel window, just enter it like a normal
Excel Function
To remove the UDF:
1. bring up the VBE window as above
2. clear the code out
3. close the VBE window
To learn more about UDFs, see:
http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
--
Gary''s Student - gsnu200901