looking for formula to identify next non-empty cell on another sheet

[Pierre]

Need a formula to identify the next non-empty cell in a column. This
reference woud be used on another worksheet to do lookups against.

Sheet 1 would have a value in cell C5. The next populated cell in
column C on sheet 1 would be C13.
On sheet 2, I'd like to identify that C13 is the starting point and so
on. This would continue for up to 6 sheets, with sheet 3, finding the
next non-empty cell after the one it found in sheet 2 C13, (sheet 3
finding the next non empty cell after C13 that it sound for sheet 2,
lets say C22 from sheet 1 and each subsequent sheet identifying the
next non empty cell in the sequence from sheet 1.

Ideas?

Thanks in advance.

Pierre

 

[T. Valko]

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?

 

[Pierre]

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

 

[T. Valko]

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(Sheet1!C5:C100,SMALL(IF(Sheet1!C5:C100<>"",ROW(Sheet1!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)

 

[T. Valko]

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))

 

[Pierre]

Ok...


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(Sheet1!C5:C100,SMALL(IF(She­et1!C5:C100<>"",ROW(Sheet1!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












- 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

 

[T. Valko]

Ok...


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(Sheet1!C5:C100,SMALL(IF(She­et1!C5:C100<>"",ROW(Sheet1!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












- 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

==================================

Ok...

What is the actual sheet name? Is it simply 1 or is it Sheet 1 or is it
Sheet1? If a sheet name is a number (like 1) or has spaces in it (like Sheet
1) then to refer to that sheet you need to use apostrophes:

='1'!A1
='Sheet 1'!A1

So the "empty" cells are not really empty? If they're not empty then
*exactly* what do they contain?

the next "different item" in column C.

Hmmm...

So, is this what you have:

12345-123
12345-123 - hidden using conditional formatting
12345-123 - hidden using conditional formatting
55555-555
99999-999
99999-999 - hidden using conditional formatting
99999-999 - hidden using conditional formatting

If that's what we're really dealing with this makes things a lot more
complicated!

 

[Pierre]

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

==================================

Ok...

What is the actual sheet name? Is it simply 1 or is it Sheet 1 or is it
Sheet1? If a sheet name is a number (like 1) or has spaces in it (like Sheet
1) then to refer to that sheet you need to use apostrophes:

='1'!A1
='Sheet 1'!A1

So the "empty" cells are not really empty? If they're not empty then
*exactly* what do they contain?


Hmmm...

So, is this what you have:

12345-123
12345-123 - hidden using conditional formatting
12345-123 - hidden using conditional formatting
55555-555
99999-999
99999-999 - hidden using conditional formatting
99999-999 - hidden using conditional formatting

If that's what we're really dealing with this makes things a lot more
complicated!

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -

Biff:
It's:
123456-123
123456-123 hidden using CF
123456-123 hidden using CF
123456-123 hidden using CF
789547-123
789547-123hidden using CF
789547-123hidden using CF
789547-123hidden using CF
789547-123hidden using CF
789547-123hidden using CF
789547-123hidden using CF
065432-123
065432-123 hidden using CF
065432-123 hidden using CF
065432-123 hidden using CF
etc

6 characters, a dash, 3 characters.

Thanks.

Pierre

 

[T. Valko]

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

==================================

Ok...

What is the actual sheet name? Is it simply 1 or is it Sheet 1 or is it
Sheet1? If a sheet name is a number (like 1) or has spaces in it (like
Sheet
1) then to refer to that sheet you need to use apostrophes:

='1'!A1
='Sheet 1'!A1

So the "empty" cells are not really empty? If they're not empty then
*exactly* what do they contain?


Hmmm...

So, is this what you have:

12345-123
12345-123 - hidden using conditional formatting
12345-123 - hidden using conditional formatting
55555-555
99999-999
99999-999 - hidden using conditional formatting
99999-999 - hidden using conditional formatting

If that's what we're really dealing with this makes things a lot more
complicated!

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -

Biff:
It's:
123456-123
123456-123 hidden using CF
123456-123 hidden using CF
123456-123 hidden using CF
789547-123
789547-123hidden using CF
789547-123hidden using CF
789547-123hidden using CF
789547-123hidden using CF
789547-123hidden using CF
789547-123hidden using CF
065432-123
065432-123 hidden using CF
065432-123 hidden using CF
065432-123 hidden using CF
etc

6 characters, a dash, 3 characters.

Thanks.

Pierre
=============================

Ok, I'm taking the easy way out on this one.

Based on your posted sample data the range is C5:C19

Use a helper column to identify the distinct entries.

Enter this formula in D5 and copy down to D19:

=IF(COUNTIF(C$5:C5,C5)=1,ROW(),"")

Then, on each sheet enter this formula and change "n" (2 places) to indicate
which item you want:

=IF(COUNT(Sheet1!D519)>=n,LEFT(INDEX(Sheet1!C5:C19,MATCH(SMALL(Sheet1!D519,n),Sheet1!D519,0)),6),"")

So, on sheet2 n = 1
sheet3 n = 2
sheet4 n = 3
sheet5 n = 4
etc
etc

 

[Pierre]

Biff:
It's:
123456-123
123456-123 hidden using CF
123456-123 hidden using CF
123456-123 hidden using CF
789547-123
789547-123hidden using CF
789547-123hidden using CF
789547-123hidden using CF
789547-123hidden using CF
789547-123hidden using CF
789547-123hidden using CF
065432-123
065432-123 hidden using CF
065432-123 hidden using CF
065432-123 hidden using CF
etc

6 characters, a dash, 3 characters.

Thanks.

Pierre
=============================

Ok, I'm taking the easy way out on this one.

Based on your posted sample data the range is C5:C19

Use a helper column to identify the distinct entries.

Enter this formula in D5 and copy down to D19:

=IF(COUNTIF(C$5:C5,C5)=1,ROW(),"")

Then, on each sheet enter this formula and change "n" (2 places) to indicate
which item you want:

=IF(COUNT(Sheet1!D519)>=n,LEFT(INDEX(Sheet1!C5:C19,MATCH(SMALL(Sheet1!D5:­D19,n),Sheet1!D519,0)),6),"")

So, on sheet2 n = 1
sheet3 n = 2
sheet4 n = 3
sheet5 n = 4
etc
etc

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -

Biff, you went the extra mile. Works like a champ. Much gratitude,
yet again..

Pierre

 

[T. Valko]

Biff:
It's:
123456-123
123456-123 hidden using CF
123456-123 hidden using CF
123456-123 hidden using CF
789547-123
789547-123hidden using CF
789547-123hidden using CF
789547-123hidden using CF
789547-123hidden using CF
789547-123hidden using CF
789547-123hidden using CF
065432-123
065432-123 hidden using CF
065432-123 hidden using CF
065432-123 hidden using CF
etc

6 characters, a dash, 3 characters.

Thanks.

Pierre
=============================

Ok, I'm taking the easy way out on this one.

Based on your posted sample data the range is C5:C19

Use a helper column to identify the distinct entries.

Enter this formula in D5 and copy down to D19:

=IF(COUNTIF(C$5:C5,C5)=1,ROW(),"")

Then, on each sheet enter this formula and change "n" (2 places) to
indicate
which item you want:

=IF(COUNT(Sheet1!D519)>=n,LEFT(INDEX(Sheet1!C5:C19,MATCH(SMALL(Sheet1!D5:­D19,n),Sheet1!D519,0)),6),"")

So, on sheet2 n = 1
sheet3 n = 2
sheet4 n = 3
sheet5 n = 4
etc
etc

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -

Biff, you went the extra mile. Works like a champ. Much gratitude,
yet again..

Pierre
===========================

You're welcome. Thanks for the feedback!