In sheet2 B1 enter:
"data"
in sheet2 B2 enter:

=IF(Sheet1!B2="STATUS",Sheet1!B1,"") and copy down. If the data in column B
of sheet1 looks like:

1
2
3
4
STATUS
6
7
8
9
10
11
STATUS
13
14
15
16
17
18
19
20
21
22
23
STATUS
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

then the data on sheet2 will look like:

data



4






11











23

To eliminate blank rows on sheet2, switch-on AutoFilter.

--
Gary''s Student - gsnu200738


"Sarah (OGI)" wrote:

I have a workbook, where on Sheet 1 there is data from B5:E (the number of
rows may be different on different workbooks).
The word 'Status' appears numerous times within Column B. I would like to
have a formula on Sheet 2 that looks for every instance of the word 'Status'
and lists the value of the cell immediately above it.
Any ideas?