On Oct 1, 12:40 pm, "T. Valko" wrote:
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(She*et1!C5:C100<"",ROW(Shee t1!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- Hide quoted text -

- Show quoted text -

Biff: Recd: #NAME USING
{=IF(COUNTA(sheet '1'!C5:C100)<1,"",TEXT(LEFT(INDEX(sheet '1'!
C5:C100,SMALL(IF(sheet '1'!C5:C100<"",ROW(sheet '1'!C5:C100)-
ROW(sheet '1'!C5)
+1),1)),6),"000000"))}

3 changes:
The blank cells on sheet 1were not really blank making it unnecessary
to look for the blank cell, just conditionally formatted to represent
such. iI feel so 'toopid on that one.
Entering crtl shift enter brought up a "save as" dialogue box,
chicking enter just made it go away.
It places ' around the number 1, as in sheet '1'

Still need to look up and enter into the top of each sheet the next
"different item" in column C. Thank you for your patience.

Where do you want the pizzas sent?

Pierre