We can shorten that slightly. No need for the TEXT() function. Still entered
as an array:

=IF(COUNTA(Sheet1!C5:C100)<n,"",LEFT(INDEX(Sheet1! C5:C100,SMALL(IF(Sheet1!C5:C100<"",ROW(Sheet1!C5: C100)-ROW(Sheet1!C5)+1),n)),5))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Ok...

The data type is text, taking on an appearance such as: "06548-001",
or "85282-001".

I'm going to assume that all the entries follow the same format:

12345-123

5 digits dash 3 digits

Assume the data of interest is on Sheet1 in the range C5:C100.

You want the formula entered on each sheet in cell A1.

Try this array formula** and change "n" (in 2 places) to the appropriate
number of the item you want:

=IF(COUNTA(Sheet1!C5:C100)<n,"",TEXT(LEFT(INDEX(Sh eet1!C5:C100,SMALL(IF(Sheet1!C5:C100<"",ROW(Sheet 1!C5:C100)-ROW(Sheet1!C5)+1),n)),5),"00000"))

For example, on the first sheet n would equal 1, on the second sheet n
would equal 2, third sheet n equals 3, etc.

This formula returns a TEXT string.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Pierre" wrote in message
ups.com...
On Sep 30, 10:01 pm, "T. Valko" wrote:
Do you want the address of the next non-empty cell or the value
contained in
the next non-empty cell?

I take it C5 is the beginning of the range and you want to find the next
cell below that that is not empty. What type of data is it, text,
numeric,
both?

You mentioned 6 sheets. Will there always be 5 entries in this range?
Are
any of the entries duplicates?

--

Biff, we'd need the value contained in the 1st non-empty cell. And on
sheet 3, the next value of the non-empty cell that it found on sheet
1.

The data type is text, taking on an appearance such as: "06548-001",
or "85282-001". Forgot to mention, on the sheet that the cells value
gets posted to, the "-001" (or last 4 characters) would have to be
stripped off, leaving just the "06548"

The range would have no duplicates, but may contain a single entry; or
as many as 20.

Thanks for your interest.
I'd use a macro, with down arrows, copy, changing sheets, paste etc.,
but when I paste the found value to the top of a sheet, it'll execute
a sql type script and refresh the data on that sheet, and may tie
things up a bit. Prefer to have a formula which would populate the
sheets all at once, and then it'd refresh on its own.

I'm just the messenger. Thanks again.

Piere