How can you write a formula that searches for a particular word?
 

I have approximately 30 worksheets in one workbook. I have one worksheet
called, "Search Template" where I want to be able to type in a word in a
particular cell (say, D6) and have Excel look through all the other
worksheets in the workbook and pull back the information about that word.
For example, if I type in the word LOAN on the "Search Template" worksheet in
cell D6, I want Excel to find the word LOAN in the other worksheets and pull
back the entire row(s) of information where that word, LOAN is found.

How can you write a formula that searches for a particular word?
 

I don't know what you mean by "pull back", but this macro will search the
cells in the worksheets and report back the sheet and cell in which the value
was found:

Sub gsnu()
Dim ws As Worksheet
Dim r As Range

v = Sheets("Search Template").Range("D6").Value

For Each ws In Worksheets
If ws.Name < "Search Template" Then
ws.Activate
For Each r In ActiveSheet.UsedRange
If InStr(1, r.Value, v) 0 Then
MsgBox (ws.Name & " " & r.Address)
End If
Next
End If
Next
End Sub


--
Gary's Student


"trainer07" wrote:

I have approximately 30 worksheets in one workbook. I have one worksheet
called, "Search Template" where I want to be able to type in a word in a
particular cell (say, D6) and have Excel look through all the other
worksheets in the workbook and pull back the information about that word.
For example, if I type in the word LOAN on the "Search Template" worksheet in
cell D6, I want Excel to find the word LOAN in the other worksheets and pull
back the entire row(s) of information where that word, LOAN is found.