You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
Biff (T Valko),

Your first formula works perfectly on the actual data.

I did not try the second formula.

You may be interested to know that the actual data contains over 8000
rows,
and there are four different column C's, for a total of 32,000 cells.

This was hugely important for my project. Thanks a lot!
--
Art


"T. Valko" wrote:

This works but seems to be overly complicated. It's the only thing I
could
get to work based on every possible combination of data I tried. Shorter
formulas worked on your data as posted but when I changed the data they
crashed.

What version of Excel are you using?

This will work in all versions:

=IF(B10,LOOKUP("zzzzzz",CHOOSE({1,2},"",INDEX(A1: A$9,MATCH("?*",INDEX(A1:A$9,1):INDEX(A1:A$9,B1),0) ))),"")

This will only work in Excel 2007:

=IF(B10,IFERROR(INDEX(A1:A$9,MATCH("?*",INDEX(A1: A$9,1):INDEX(A1:A$9,B1),0)),""),"")

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
Ok, sorry fellas. Here is my entire issue:

_________A________B________C
1________""________4________Pear
2________""________0________""
3________Pear______0________""
4________""________0________""
5________""________2________""
6________""________0________""
7________""________3________Fig
8________Fig_______0________""
9________""________0________""

Column A contains a formula that evaluates to a string. Some are empty
as
shown. These are a series of datasets. The information in Col B is used
to
define the position and size of each dataset. For instance, B1, which
is
4,
means that a dataset begins in this row and is 4 rows tall. B2, which
is
zero, means that this row does not start a new dataset.

Each dataset may contain all empty strings or one and only one
non-empty
string. In the example, the second dataset (A5:A6) is all empty
strings.

I need a formula for Column C that on the first row of each dataset
returns
the non-empty string in the dataset. This formula will return an empty
string
if there are no non-empty strings in the dataset or if this row is not
the
first row of a dataset.

If the solution is an overly complex formula, as I suspect it is, it is
OK
to break it up into more than one column.

Thanks a lot!
--
Art


"Rick Rothstein" wrote:

For future consideration: You should **not** simplify your questions
for
us... the answers you get for questions you ask tend to be tailored to
the
conditions you say you have... if you don't tell us what those
conditions
actually are, you will usually get answers that will need to be
modified
when you actually do tell us what the conditions are, so you might as
well
tell them to us at the beginning and save those who you are asking to
help
you from having to revisit your question a second time.

--
Rick (MVP - Excel)


"Art" wrote in message
...
Yes, but the number of rows in the column will vary. That's another
problem I
have which I'm going to post now.
--
Art


"Rick Rothstein" wrote:

Given the conditions you stated, it seems this should work...

=A1&A2&A3&A4

--
Rick (MVP - Excel)


"Art" wrote in message
...
_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the
cells
contains
a text entry (cell A3 in this example). I want to enter a formula
into
A5
that will return this text entry.

Thanks,
Art

.


.



.