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,

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,

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,

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,

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,

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,

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,

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,

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,

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,

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,