Array reference from current row to bottom of column

J

JM

Help! I'm struggling with the following problem:

I'd like to create an array reference that begins at the
current row (in a specified column) and extends to the
bottom of the column. This array reference must
insensitive to sorting the rows (based on other columns).

Illustrative example:
Let column A contain
{2,13,4,15,"",8,9,"",10,11,3,6,6,12,19}
Then in column B, I need array references to:
{A1:A15, A2:A15, A3:15, ...}
And if the order of rows are changed the function changes
its array reference accordingly. E.g., row 3 is swapped
with row 2, then
Column A is:
{2,4, 13,15,"",8,9,"",10,11,3,6,6,12,19} (note the swap
of data in elements 2 and 3)
BUT Column B should adjust to refer to the current row to
the bottom of column A
{A1:A15, A2:A15, A3:15, ...}

The array reference will be used to complete the "real"
problem. However, I believe I understand how to do the
rest. (For those interested, I'll wrap this array
reference in a more complicated single cell array
function. The steps will be to search the identified
array for the top most 10 non-empty elements. Then of
these 10 non-empty elements, sum the smallest 5.)

Any help would be greatly appreciated.

Thanks,
JM
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top