Find resulting text in column of functions
 

Cells A3:A100 each contain a formula which will result in either a name or "
". How can I retreive that name when I don't know which row it will be in.
I have many columns of similar formulas and each name will appear in a
different row.
I have tried the lookup, but because each cell contains a formula, I believe
it considers every cell not blank. I hope this is clear.

Thanks,

Find resulting text in column of functions
 

Sherri,

I'm a little confused. Can you clarify a little more? When you say you're
looking for "that name," are you looking for a specific name out of many or
are you looking for the first nonblank "" name in that column?

Can you perhaps provide an example?

Thanks!


"Sherri" wrote:

Cells A3:A100 each contain a formula which will result in either a name or "
". How can I retreive that name when I don't know which row it will be in.
I have many columns of similar formulas and each name will appear in a
different row.
I have tried the lookup, but because each cell contains a formula, I believe
it considers every cell not blank. I hope this is clear.

Thanks,

Find resulting text in column of functions
 

I am looking for the first nonblank in the column. The name will be
different and in a different row every time. Some columns will have more
than one name which I will need to access, but I am pretty sure I could do
that after I figure out how to get the first one. Does that help?
Sherri

"GSnyder" wrote:

Sherri,

I'm a little confused. Can you clarify a little more? When you say you're
looking for "that name," are you looking for a specific name out of many or
are you looking for the first nonblank "" name in that column?

Can you perhaps provide an example?

Thanks!


"Sherri" wrote:

Cells A3:A100 each contain a formula which will result in either a name or "
". How can I retreive that name when I don't know which row it will be in.
I have many columns of similar formulas and each name will appear in a
different row.
I have tried the lookup, but because each cell contains a formula, I believe
it considers every cell not blank. I hope this is clear.

Thanks,

Find resulting text in column of functions
 

Assuming the blank cells contain formula blnaks (""):

=INDEX(A2:A15,MATCH(TRUE,INDEX(A2:A15<"",,1),0))

--
Biff
Microsoft Excel MVP


"Sherri" wrote in message
...
I am looking for the first nonblank in the column. The name will be
different and in a different row every time. Some columns will have more
than one name which I will need to access, but I am pretty sure I could do
that after I figure out how to get the first one. Does that help?
Sherri

"GSnyder" wrote:

Sherri,

I'm a little confused. Can you clarify a little more? When you say
you're
looking for "that name," are you looking for a specific name out of many
or
are you looking for the first nonblank "" name in that column?

Can you perhaps provide an example?

Thanks!


"Sherri" wrote:

Cells A3:A100 each contain a formula which will result in either a name
or "
". How can I retreive that name when I don't know which row it will be
in.
I have many columns of similar formulas and each name will appear in a
different row.
I have tried the lookup, but because each cell contains a formula, I
believe
it considers every cell not blank. I hope this is clear.

Thanks,

Find resulting text in column of functions
 

G
I am looking for the non blank answer because it could be any name.
Let's say I have 20 people, working in 6 possible locations, in 2 possible
positions and with varied availability. I am trying to get a schedule
established, so I have a column in another sheet for location1-position1 and
depending on availability someone's name will show up in that column. So it
goes for location1-position2, etc.
Sometimes 2 or 3 people will rotate to fill the position, so that column
will have 2 or 3 names.
I have the name(s) show up in the column, but I can't transfer them into a
schedule on another sheet.

"GSnyder" wrote:

Sherri,

I'm a little confused. Can you clarify a little more? When you say you're
looking for "that name," are you looking for a specific name out of many or
are you looking for the first nonblank "" name in that column?

Can you perhaps provide an example?

Thanks!


"Sherri" wrote:

Cells A3:A100 each contain a formula which will result in either a name or "
". How can I retreive that name when I don't know which row it will be in.
I have many columns of similar formulas and each name will appear in a
different row.
I have tried the lookup, but because each cell contains a formula, I believe
it considers every cell not blank. I hope this is clear.

Thanks,

Find resulting text in column of functions
 

T. Valko,

That worked great! I am unfamiliar with the index function, so I have
another question. If there are 2 names in the column, can I get both to show
up somewhere?

Thanks a lot!
Sherri

"Sherri" wrote:

G
I am looking for the non blank answer because it could be any name.
Let's say I have 20 people, working in 6 possible locations, in 2 possible
positions and with varied availability. I am trying to get a schedule
established, so I have a column in another sheet for location1-position1 and
depending on availability someone's name will show up in that column. So it
goes for location1-position2, etc.
Sometimes 2 or 3 people will rotate to fill the position, so that column
will have 2 or 3 names.
I have the name(s) show up in the column, but I can't transfer them into a
schedule on another sheet.

"GSnyder" wrote:

Sherri,

I'm a little confused. Can you clarify a little more? When you say you're
looking for "that name," are you looking for a specific name out of many or
are you looking for the first nonblank "" name in that column?

Can you perhaps provide an example?

Thanks!


"Sherri" wrote:

Cells A3:A100 each contain a formula which will result in either a name or "
". How can I retreive that name when I don't know which row it will be in.
I have many columns of similar formulas and each name will appear in a
different row.
I have tried the lookup, but because each cell contains a formula, I believe
it considers every cell not blank. I hope this is clear.

Thanks,

Find resulting text in column of functions
 

Are you saying that there will only be 1 or 2 names in the column?

If there are 2 or more names this will return the *last* name:

=LOOKUP(2,1/(A2:A15<""),A2:A15)

Note that if there is only 1 name in the column that 1 name will be *both*
the first name and the last name at the same time!

--
Biff
Microsoft Excel MVP


"Sherri" wrote in message
...
T. Valko,

That worked great! I am unfamiliar with the index function, so I have
another question. If there are 2 names in the column, can I get both to
show
up somewhere?

Thanks a lot!
Sherri

"Sherri" wrote:

G
I am looking for the non blank answer because it could be any name.
Let's say I have 20 people, working in 6 possible locations, in 2
possible
positions and with varied availability. I am trying to get a schedule
established, so I have a column in another sheet for location1-position1
and
depending on availability someone's name will show up in that column. So
it
goes for location1-position2, etc.
Sometimes 2 or 3 people will rotate to fill the position, so that column
will have 2 or 3 names.
I have the name(s) show up in the column, but I can't transfer them into
a
schedule on another sheet.

"GSnyder" wrote:

Sherri,

I'm a little confused. Can you clarify a little more? When you say
you're
looking for "that name," are you looking for a specific name out of
many or
are you looking for the first nonblank "" name in that column?

Can you perhaps provide an example?

Thanks!


"Sherri" wrote:

Cells A3:A100 each contain a formula which will result in either a
name or "
". How can I retreive that name when I don't know which row it will
be in.
I have many columns of similar formulas and each name will appear in
a
different row.
I have tried the lookup, but because each cell contains a formula, I
believe
it considers every cell not blank. I hope this is clear.

Thanks,

Find resulting text in column of functions
 

Biff,

There could be 1-3 names (Mary T.,Tony W., Bill F.) in a column. The lookup
which you just gave me returned " " (the first cell in the row?). Could
this be because it considers the formula in the cell as not blank?

Thanks again,
Sherri


"T. Valko" wrote:

Are you saying that there will only be 1 or 2 names in the column?

If there are 2 or more names this will return the *last* name:

=LOOKUP(2,1/(A2:A15<""),A2:A15)

Note that if there is only 1 name in the column that 1 name will be *both*
the first name and the last name at the same time!

--
Biff
Microsoft Excel MVP


"Sherri" wrote in message
...
T. Valko,

That worked great! I am unfamiliar with the index function, so I have
another question. If there are 2 names in the column, can I get both to
show
up somewhere?

Thanks a lot!
Sherri

"Sherri" wrote:

G
I am looking for the non blank answer because it could be any name.
Let's say I have 20 people, working in 6 possible locations, in 2
possible
positions and with varied availability. I am trying to get a schedule
established, so I have a column in another sheet for location1-position1
and
depending on availability someone's name will show up in that column. So
it
goes for location1-position2, etc.
Sometimes 2 or 3 people will rotate to fill the position, so that column
will have 2 or 3 names.
I have the name(s) show up in the column, but I can't transfer them into
a
schedule on another sheet.

"GSnyder" wrote:

Sherri,

I'm a little confused. Can you clarify a little more? When you say
you're
looking for "that name," are you looking for a specific name out of
many or
are you looking for the first nonblank "" name in that column?

Can you perhaps provide an example?

Thanks!


"Sherri" wrote:

Cells A3:A100 each contain a formula which will result in either a
name or "
". How can I retreive that name when I don't know which row it will
be in.
I have many columns of similar formulas and each name will appear in
a
different row.
I have tried the lookup, but because each cell contains a formula, I
believe
it considers every cell not blank. I hope this is clear.

Thanks,

Find resulting text in column of functions
 

Could this be because it considers the formula
in the cell as not blank?

Yes. If you have formulas like this:

=IF(something=something,something," ")

That formula returns a *space character* if the logical test if false. That
is not a formula blank. This is how you return a formula blank:

=IF(something=something,something,"")

Now, if you have 1 to 3 names in a column and you want to return *all* names
it gets more complicated!

Make sure you fix those other formulas to return formula blanks.

Let's assume you want to extract *all* the names from the range A2:A15 into
a list starting in cell C2.

Enter this array formula** in C2 and copy down until you get blanks (formula
blanks):

=IF(ROWS(C$2:C2)<=COUNTIF(A$2:A$15,"?*"),INDEX(A$2 :A$15,SMALL(IF(A$2:A$15<"",ROW(A$2:A$15)),ROWS(C$ 2:C2))-MIN(ROW(A$2:A$15))+1),"")

If you'll have at most 3 names in column A then you need to copy the formula
to at least 3 cells.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"Sherri" wrote in message
...
Biff,

There could be 1-3 names (Mary T.,Tony W., Bill F.) in a column. The
lookup
which you just gave me returned " " (the first cell in the row?). Could
this be because it considers the formula in the cell as not blank?

Thanks again,
Sherri


"T. Valko" wrote:

Are you saying that there will only be 1 or 2 names in the column?

If there are 2 or more names this will return the *last* name:

=LOOKUP(2,1/(A2:A15<""),A2:A15)

Note that if there is only 1 name in the column that 1 name will be
*both*
the first name and the last name at the same time!

--
Biff
Microsoft Excel MVP


"Sherri" wrote in message
...
T. Valko,

That worked great! I am unfamiliar with the index function, so I have
another question. If there are 2 names in the column, can I get both
to
show
up somewhere?

Thanks a lot!
Sherri

"Sherri" wrote:

G
I am looking for the non blank answer because it could be any name.
Let's say I have 20 people, working in 6 possible locations, in 2
possible
positions and with varied availability. I am trying to get a schedule
established, so I have a column in another sheet for
location1-position1
and
depending on availability someone's name will show up in that column.
So
it
goes for location1-position2, etc.
Sometimes 2 or 3 people will rotate to fill the position, so that
column
will have 2 or 3 names.
I have the name(s) show up in the column, but I can't transfer them
into
a
schedule on another sheet.

"GSnyder" wrote:

Sherri,

I'm a little confused. Can you clarify a little more? When you say
you're
looking for "that name," are you looking for a specific name out of
many or
are you looking for the first nonblank "" name in that column?

Can you perhaps provide an example?

Thanks!


"Sherri" wrote:

Cells A3:A100 each contain a formula which will result in either a
name or "
". How can I retreive that name when I don't know which row it
will
be in.
I have many columns of similar formulas and each name will appear
in
a
different row.
I have tried the lookup, but because each cell contains a formula,
I
believe
it considers every cell not blank. I hope this is clear.

Thanks,

Find resulting text in column of functions
 

Biff,

I have replaced all the " " with "". I then copied your formula into my
worksheet, and changed the references to my actual cells. I entered it with
ctrl, shift, enter. It did not return the names, but did not error out. I
was looking in column B3:B100 and pasted your formula into cells B105:B110.
I then tried to enter your formula onto a new worksheet, referencing the
other sheet and cells, but I got the same result - nothing. I replaced your
original Index formula (which worked wonderfully) with the new IF formula and
now that first name in the list doesn't appear anymore. I tried leaving the
index formula in the C2 cell (using your references), then using the IF
formula in C3:C6 but still no luck. I really appreciate your help on this!

Sherri

"T. Valko" wrote:

Could this be because it considers the formula
in the cell as not blank?

Yes. If you have formulas like this:

=IF(something=something,something," ")

That formula returns a *space character* if the logical test if false. That
is not a formula blank. This is how you return a formula blank:

=IF(something=something,something,"")

Now, if you have 1 to 3 names in a column and you want to return *all* names
it gets more complicated!

Make sure you fix those other formulas to return formula blanks.

Let's assume you want to extract *all* the names from the range A2:A15 into
a list starting in cell C2.

Enter this array formula** in C2 and copy down until you get blanks (formula
blanks):

=IF(ROWS(C$2:C2)<=COUNTIF(A$2:A$15,"?*"),INDEX(A$2 :A$15,SMALL(IF(A$2:A$15<"",ROW(A$2:A$15)),ROWS(C$ 2:C2))-MIN(ROW(A$2:A$15))+1),"")

If you'll have at most 3 names in column A then you need to copy the formula
to at least 3 cells.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"Sherri" wrote in message
...
Biff,

There could be 1-3 names (Mary T.,Tony W., Bill F.) in a column. The
lookup
which you just gave me returned " " (the first cell in the row?). Could
this be because it considers the formula in the cell as not blank?

Thanks again,
Sherri


"T. Valko" wrote:

Are you saying that there will only be 1 or 2 names in the column?

If there are 2 or more names this will return the *last* name:

=LOOKUP(2,1/(A2:A15<""),A2:A15)

Note that if there is only 1 name in the column that 1 name will be
*both*
the first name and the last name at the same time!

--
Biff
Microsoft Excel MVP


"Sherri" wrote in message
...
T. Valko,

That worked great! I am unfamiliar with the index function, so I have
another question. If there are 2 names in the column, can I get both
to
show
up somewhere?

Thanks a lot!
Sherri

"Sherri" wrote:

G
I am looking for the non blank answer because it could be any name.
Let's say I have 20 people, working in 6 possible locations, in 2
possible
positions and with varied availability. I am trying to get a schedule
established, so I have a column in another sheet for
location1-position1
and
depending on availability someone's name will show up in that column.
So
it
goes for location1-position2, etc.
Sometimes 2 or 3 people will rotate to fill the position, so that
column
will have 2 or 3 names.
I have the name(s) show up in the column, but I can't transfer them
into
a
schedule on another sheet.

"GSnyder" wrote:

Sherri,

I'm a little confused. Can you clarify a little more? When you say
you're
looking for "that name," are you looking for a specific name out of
many or
are you looking for the first nonblank "" name in that column?

Can you perhaps provide an example?

Thanks!


"Sherri" wrote:

Cells A3:A100 each contain a formula which will result in either a
name or "
". How can I retreive that name when I don't know which row it
will
be in.
I have many columns of similar formulas and each name will appear
in
a
different row.
I have tried the lookup, but because each cell contains a formula,
I
believe
it considers every cell not blank. I hope this is clear.

Thanks,

Find resulting text in column of functions
 

Here's a small sample file that demonstrates this:

xSherri.xls 14kb

http://cjoint.com/?dif6eOyzOI

I use the array formula to extract all the names and I use another (simpler)
formula to extract the first name in the list.

--
Biff
Microsoft Excel MVP


"Sherri" wrote in message
...
Biff,

I have replaced all the " " with "". I then copied your formula into my
worksheet, and changed the references to my actual cells. I entered it
with
ctrl, shift, enter. It did not return the names, but did not error out. I
was looking in column B3:B100 and pasted your formula into cells
B105:B110.
I then tried to enter your formula onto a new worksheet, referencing the
other sheet and cells, but I got the same result - nothing. I replaced
your
original Index formula (which worked wonderfully) with the new IF formula
and
now that first name in the list doesn't appear anymore. I tried leaving
the
index formula in the C2 cell (using your references), then using the IF
formula in C3:C6 but still no luck. I really appreciate your help on
this!

Sherri

"T. Valko" wrote:

Could this be because it considers the formula
in the cell as not blank?

Yes. If you have formulas like this:

=IF(something=something,something," ")

That formula returns a *space character* if the logical test if false.
That
is not a formula blank. This is how you return a formula blank:

=IF(something=something,something,"")

Now, if you have 1 to 3 names in a column and you want to return *all*
names
it gets more complicated!

Make sure you fix those other formulas to return formula blanks.

Let's assume you want to extract *all* the names from the range A2:A15
into
a list starting in cell C2.

Enter this array formula** in C2 and copy down until you get blanks
(formula
blanks):

=IF(ROWS(C$2:C2)<=COUNTIF(A$2:A$15,"?*"),INDEX(A$2 :A$15,SMALL(IF(A$2:A$15<"",ROW(A$2:A$15)),ROWS(C$ 2:C2))-MIN(ROW(A$2:A$15))+1),"")

If you'll have at most 3 names in column A then you need to copy the
formula
to at least 3 cells.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.


--
Biff
Microsoft Excel MVP


"Sherri" wrote in message
...
Biff,

There could be 1-3 names (Mary T.,Tony W., Bill F.) in a column. The
lookup
which you just gave me returned " " (the first cell in the row?).
Could
this be because it considers the formula in the cell as not blank?

Thanks again,
Sherri


"T. Valko" wrote:

Are you saying that there will only be 1 or 2 names in the column?

If there are 2 or more names this will return the *last* name:

=LOOKUP(2,1/(A2:A15<""),A2:A15)

Note that if there is only 1 name in the column that 1 name will be
*both*
the first name and the last name at the same time!

--
Biff
Microsoft Excel MVP


"Sherri" wrote in message
...
T. Valko,

That worked great! I am unfamiliar with the index function, so I
have
another question. If there are 2 names in the column, can I get
both
to
show
up somewhere?

Thanks a lot!
Sherri

"Sherri" wrote:

G
I am looking for the non blank answer because it could be any name.
Let's say I have 20 people, working in 6 possible locations, in 2
possible
positions and with varied availability. I am trying to get a
schedule
established, so I have a column in another sheet for
location1-position1
and
depending on availability someone's name will show up in that
column.
So
it
goes for location1-position2, etc.
Sometimes 2 or 3 people will rotate to fill the position, so that
column
will have 2 or 3 names.
I have the name(s) show up in the column, but I can't transfer
them
into
a
schedule on another sheet.

"GSnyder" wrote:

Sherri,

I'm a little confused. Can you clarify a little more? When you
say
you're
looking for "that name," are you looking for a specific name out
of
many or
are you looking for the first nonblank "" name in that column?

Can you perhaps provide an example?

Thanks!


"Sherri" wrote:

Cells A3:A100 each contain a formula which will result in
either a
name or "
". How can I retreive that name when I don't know which row it
will
be in.
I have many columns of similar formulas and each name will
appear
in
a
different row.
I have tried the lookup, but because each cell contains a
formula,
I
believe
it considers every cell not blank. I hope this is clear.

Thanks,