M
Morton Detwyler
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!
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!