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
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