PLEASE HELP!! FORMULA TO RETURN A SHEETS NAME/REFERENCE.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to find a formula in Exel 97 so that I am able to find a text
value on multiple sheets in a workbook and it will return the name of the
sheet for me. I have two spreadsheets open and want to find out what sheet/s
a cell reference is on: example. xyz is on sheet ?? and sheet ?? there are
mutiple sheets which can have that reference on them but for me to go through
them all would take days. Is this possible to do in a formula?
 
I am not sure if this works with xl97 but it certainly works with later
versions.

Right click on one of the worksheet names at the bottom of the screen.
Click on select all sheets.
Select menu item Edit->Find.
Enter the text to find and then click on Find All.
If it works, you should have a list at the bottom of the dialogue box with
the sheets and cell addresses of all occurrences of the text. Click on any
one of them and it will take you to the cell.
You should be able to click on the cell and edit it without loosing the
dialogue box with the list. Note the value changes in the dialog box but you
don't loose the sheet name and cell address.

I will appreciate it if you let me know if this works in xl97.

Regards,

OssieMac
 
Thankyou but no go, it didn't bring up any dialogue box with info and would
only check that one sheet that I had open - probably coz its xl97.

Im really after a formula that I would be able to copy as there are over 100
values that i want to lookup. In my workbook I have all the values i wish to
look up on my first sheet in the first column and in the next column i would
like it to have the sheet reference/result of my formula. I have another 20
worksheets in the same workbook that I want the formula to check for the
specified value and return the worksheet name (or in my case i have named
each sheet with a date) so im really asking it to return what date(sheet) the
value is on. Some could be on multiple sheets so I need it to return all
dates/sheets related. Im not even sure that xl97 would be capable of doing
that.

I hope Im making sense, im rather a novice with formulas?? Are you able to
help still?

Chaz
 
Sorry Chaz I don't think that I can help any more. It is easy enough to do
with a macro but I am reluctant to try that because I cannot test it with
xl97.

Regards,

OssieMac
 
Chaz,
I think that the macro code below will do the trick for you, and I believe
it will run on '97.

To put this code into your workbook:
Choose Tools | Macro and select the Visual Basic Editor from the list.
From the VB Editor's menu choose Insert | Module
Copy the code below and paste it into the module that opens. Change the
name of the worksheet and first list cell address in the code if you need to.
Close the VB Editor. Then back in the workbook, choose Tools | Macro |
Macros and choose the [ReportTextFound] macro and click the [Run] button. If
it fails, hit the [Debug] button and let me know what line is highlighted -
that will be the line that created the error. I don't think it'll error out
on you, but i could be wrong - there may be something in my Selection.Find
statement that Excel 97 doesn't like.

Sub ReportTextFound()
'change this sheet name definition to
'the name of the sheet with your list
'of text to find
Const myListSheet = "Sheet1"

'change this to the address of the
'first cell on the list sheet with
'text to be found
Const listStart = "A1"

Dim anySheet As Worksheet
Dim searchFor As String
Dim baseCell As Range
Dim rOffset As Integer
Dim cOffset As Integer

Worksheets(myListSheet).Select
Range(listStart).Select
Set baseCell = Worksheets(myListSheet).Range(listStart)
Application.ScreenUpdating = False ' faster
For Each anySheet In Worksheets
If anySheet.Name <> myListSheet Then
cOffset = cOffset + 1
rOffset = 0
anySheet.Activate
'list must not have gaps in it, it will
'stop on an empty cell
Do Until IsEmpty(baseCell.Offset(rOffset, 0))
searchFor = baseCell.Offset(rOffset, 0)
anySheet.Cells.Select
On Error Resume Next
Selection.Find(What:=searchFor, After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate
If Err = 0 Then
'found match
baseCell.Offset(rOffset, cOffset) = anySheet.Name
Else
Err.Clear
End If
On Error GoTo 0
rOffset = rOffset + 1
Loop ' list loop
Range("A1").Select ' just for neatness
End If
Next ' look at another sheet
Worksheets(myListSheet).Select
Application.ScreenUpdating = True
End Sub
 
Back
Top