"Ron Rosenfeld" wrote in message
...

On Wed, 9 Oct 2013 00:14:44 +0100, "Victor Delta" wrote:

I have an Excel spreadsheet that contains two worksheets. The first
contains
a set of data records - one row per record.

In the second worksheet, I would like to create a column which lists, in
numerical order, the row numbers of the first worksheet where the data in
column C is equal to the word 'HOME'.

I've thought of a way of doing this but it involves adding an extra hidden
column to the first worksheet which I would prefer not to do, if possible.

Can anyone please help me with a formula (an array formula perhaps?) that
will do what I want in one step.

Many thanks.


This formula must be **array-entered**:

Sheet2!A1:
=IFERROR(SMALL((Sheet1!$C$1:$C$100="HOME")*ROW(IND IRECT("1:100")),
COUNTIF(Sheet1!$C$1:$C$100,"<HOME")+ROWS($1:1))," ")

Change all of the 100's to the highest row necessary. You could reference
the entire row (Sheet1!$C:$C), but it will run more slowly
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

-----------------------------

Ron

Very many thanks for this. Have entered the formula as suggested by each
cell shows #NAME? - Have I done something silly?

V