You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Morton Detwyler" wrote in
message ...
Biff,
Thank you so much for providing both formulas. They worked perfectly and
both are applicable to my situation. Your assistance was invaluable!

"T. Valko" wrote:

There seems to be some question as to whether you want the data extracted
across a row horizontally or down a column vertically. So, here's both.
Both
formulas are array formulas**.

Across the row horizontally...

=IF(COLUMNS($AA2:AA2)<=COUNT($B2:$Z2),INDEX($B2:$Z 2,MATCH(TRUE,ISNUMBER($B2:$Z2),0)+COLUMNS($AA2:AA2 )-1),"")

Down the column vertically...

=IF(ROWS(AA$2:AA2)<=COUNT(B$2:Z$2),INDEX(B$2:Z$2,M ATCH(TRUE,ISNUMBER(B$2:Z$2),0)+ROWS(AA$2:AA2)-1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Morton Detwyler" wrote in
message ...
I pull product metric data into an Excel spreadsheet from an online OLAP
Cube
that is returned in a series of rows. Product names are in column [A],
and
number headings are in Row [1] (i.e. 1,2,3,4.....). We do not use month
names. Some newly introduced products might not have any appreciable
metric
data for the first few months of their existence, so my query returns a
blank
into the first few cells of a row. When enough data has been gathered,
metrics will begin to appear. So it's typical that some rows will have
blanks in the first few cells, and then subsequent cells are populated;
e.g.
cell A2 = Product Name, cell B2 = blank, cell C2 = blank, cell D2 =
12.57%,
etc. As each product is different, so is the number of blank cells
preceding
a metric - some products may have no blank cells, others may have many.

To the right of these metrics, beginning in cell AA2 and going down the
column, I perform a calculation on the first entered metric for each
product.
I then must perform another calculation on the second, third, etc.
metrics.
For example, AA2 = calc on first entered metric; AA3 = calc on second
entered
metric; AA4 = calc on third entered metric, etc.

I'd like my formula in this far right column (AA2) to be able to look
to
the
left and find the "first" entered metric that proceeds the blank cells.
Then
I'd like a formula in cell AA3 to look to the left and find the
"second"
entered metric that proceeds the blank cells, a formula in cell AA4 to
look
to the left and find the "third" entered metric that proceeds the blank
cells, etc.

I simply can not figure out how to do this. Any help would be greatly
appreciated and make the management of this spreadsheet so much easier.
Thank
you very much for any assistance!