INDEX, MATCH, AND OFFSET... OH MY!
 

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, MATCH, AND OFFSET... OH MY!
 

=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.

INDEX, MATCH, AND OFFSET... OH MY!
 

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.

INDEX, MATCH, AND OFFSET... OH MY!
 

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.

INDEX, MATCH, AND OFFSET... OH MY!
 

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.