Locating a phrase in a woorksheet by formula

[Tom Peacock]

I have a workbook with a number of sheets that are named by a stock ticker
symbol and contain information on the company with that ticker. I want to
extract some of this information but it is not in a consistent cell in the
sheet. Both the column and row number vary somewhat. The information is
uniformly labeled with a phrase that is unique to each sheet and is located
consistently with the label. So if I find the label I can find the data. I
want to make a table of the data with rows named by tickers and columns by
the labels such as "Gross Margin". I just can't find a way to locate the
phrase without knowing either a row or column to look in in advance. Any
suggestions?

--
Tom Peacock
7730 Meadowvale
Houston, TX 77063-6212
Cell: 832-439-5797
Fax: 713-782-5615

 

[Charlie O'Neill]

Tom,

One way would be to use the Edit/Find feature in Excel.
Highlight the entire spreadsheet, Select Edit/Find and type in *Gross
Margin* click on find next. This will find phrases such as "The Gross
Margin", "The Gross Margin Data" and "Gross Margin Data".

Charlie O'Neill

 

[=?Windows-1252?Q?H=E9ctor_Miguel?=]

hi, Tom !

...extract ... information but it is not in a consistent cell in the sheet.
... column and row number vary somewhat.
... information is uniformly labeled ... and is located consistently with the label.
... if I find the label I can find the data.
... labels such as "Gross Margin".
... can't find a way to locate the phrase without knowing either a row or column to look in in advance.

I think you could start your workaround with [something like] the following =>array<= formula...
=> replace [my] 'search_range' with a 'real' one ...AND...
=> use the formula OUT OF the 'search_range'
[formula shall be in a single line. it will locate the address of the first occurrence of 'search string']

=address(
min(if(search_range="search string",row(search_range),65536)),
min(if(search_range="search string",column(search_range),255)))

hth,
hector.

 

[Tom Peacock]

Thanks for the ideas, Ill try them.