I have text data in cells (A1:A10)
In (B1:B10) I have the following formula... =IF(A10,A1,"")
In C1 I have the following array formula...
{=INDEX(B1:B10,MATCH(TRUE,B1:B10<"",0))}
That gives me the first cell in Column B with text in it, which changes as I
remove data from Column A

And here's my question... Is there a formula that I can put in C2, that will
give me the 2nd cell in Column B, that will change as the information changes
in Column A?

I have the array formula
{=OFFSET(B1,SMALL(IF(ISTEXT(B1:B20),ROW(B1:B20)),2 )-1,0,20,1)}
That gives me the 2nd cell in Column B, but it doesn't change to B3 when A2
is removed.

=INDEX(B1:B10,SMALL(IF(B1:B10<"",ROW(B1:B10)),2))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Confused" wrote in message
...
I have text data in cells (A1:A10)
In (B1:B10) I have the following formula... =IF(A10,A1,"")
In C1 I have the following array formula...
{=INDEX(B1:B10,MATCH(TRUE,B1:B10<"",0))}
That gives me the first cell in Column B with text in it, which changes as
I
remove data from Column A

And here's my question... Is there a formula that I can put in C2, that
will
give me the 2nd cell in Column B, that will change as the information
changes
in Column A?

I have the array formula
{=OFFSET(B1,SMALL(IF(ISTEXT(B1:B20),ROW(B1:B20)),2 )-1,0,20,1)}
That gives me the 2nd cell in Column B, but it doesn't change to B3 when
A2
is removed.

OMG! You're a GENIUS, Bob! Thank you so much! I knew there was someone
smarter than me out there that could help! Thank you thank you thank you. =)

"Bob Phillips" wrote:

=INDEX(B1:B10,SMALL(IF(B1:B10<"",ROW(B1:B10)),2))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Confused" wrote in message
...
I have text data in cells (A1:A10)
In (B1:B10) I have the following formula... =IF(A10,A1,"")
In C1 I have the following array formula...
{=INDEX(B1:B10,MATCH(TRUE,B1:B10<"",0))}
That gives me the first cell in Column B with text in it, which changes as
I
remove data from Column A

And here's my question... Is there a formula that I can put in C2, that
will
give me the 2nd cell in Column B, that will change as the information
changes
in Column A?

I have the array formula
{=OFFSET(B1,SMALL(IF(ISTEXT(B1:B20),ROW(B1:B20)),2 )-1,0,20,1)}
That gives me the 2nd cell in Column B, but it doesn't change to B3 when
A2
is removed.

Oooookay... so question number 2.

If I were to add a column in between A & B, lets say Column A1

Could I put a formula in D2, that would look up the name in C2 -
=INDEX(B1:B10,SMALL(IF(B1:B10<"",ROW(B1:B10)),2))

And give me the related info in our new column A1?

"Confused" wrote:

OMG! You're a GENIUS, Bob! Thank you so much! I knew there was someone
smarter than me out there that could help! Thank you thank you thank you. =)

"Bob Phillips" wrote:

=INDEX(B1:B10,SMALL(IF(B1:B10<"",ROW(B1:B10)),2))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Confused" wrote in message
...
I have text data in cells (A1:A10)
In (B1:B10) I have the following formula... =IF(A10,A1,"")
In C1 I have the following array formula...
{=INDEX(B1:B10,MATCH(TRUE,B1:B10<"",0))}
That gives me the first cell in Column B with text in it, which changes as
I
remove data from Column A

And here's my question... Is there a formula that I can put in C2, that
will
give me the 2nd cell in Column B, that will change as the information
changes
in Column A?

I have the array formula
{=OFFSET(B1,SMALL(IF(ISTEXT(B1:B20),ROW(B1:B20)),2 )-1,0,20,1)}
That gives me the 2nd cell in Column B, but it doesn't change to B3 when
A2
is removed.

Never mind... I worked it out. =)

"Confused" wrote:

Oooookay... so question number 2.

If I were to add a column in between A & B, lets say Column A1

Could I put a formula in D2, that would look up the name in C2 -
=INDEX(B1:B10,SMALL(IF(B1:B10<"",ROW(B1:B10)),2))

And give me the related info in our new column A1?

"Confused" wrote:

OMG! You're a GENIUS, Bob! Thank you so much! I knew there was someone
smarter than me out there that could help! Thank you thank you thank you. =)

"Bob Phillips" wrote:

=INDEX(B1:B10,SMALL(IF(B1:B10<"",ROW(B1:B10)),2))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Confused" wrote in message
...
I have text data in cells (A1:A10)
In (B1:B10) I have the following formula... =IF(A10,A1,"")
In C1 I have the following array formula...
{=INDEX(B1:B10,MATCH(TRUE,B1:B10<"",0))}
That gives me the first cell in Column B with text in it, which changes as
I
remove data from Column A

And here's my question... Is there a formula that I can put in C2, that
will
give me the 2nd cell in Column B, that will change as the information
changes
in Column A?

I have the array formula
{=OFFSET(B1,SMALL(IF(ISTEXT(B1:B20),ROW(B1:B20)),2 )-1,0,20,1)}
That gives me the 2nd cell in Column B, but it doesn't change to B3 when
A2
is removed.