Hi All,
I am using Excel 2007 for Windows.
I have a table that consists of many columns and expanding rows; for this reason, I have referenced the table as a dynamic named range called “Data”. “Data” can contain duplicate values in both columns and rows. I have numeric labels in row 3, spanning the width of my table; “Data” starts in column “G”, row 4. Column “A” contains sequential numeric values (references) that can be used as relative row numbers for my table. The cells within the table are populated with a formula that returns either a numeric value or empty text (“”) showing a blank cell. Zero is a valid numeric value in the table.
Scenario:
Find the last instance of a specific numeric value (criterion) within “Data”.
From that instance, I would then like to return between 1 and more of the values located above/before the last instance of the specified criterion.
1. Find the last instance of a specific criterion within “Data”.
2. Based on last instance of the specific criterion, return the last 3 values above/before that last instance. The number of values to return will vary, and there may be no values to return.
3. Using dynamic name “Data” in a flexible formula based solution with an input cell for the criterion; input cell for quantity of values to return; error trap if fewer values available to be returned than requested; return the values across a single row.
Sample Layout:
Book5.xlsx attached
Expected Results:
Looking for last criterion of 0 (zero) in table –
Found in column “V”, row 14; return 1 value above/before criterion = 7
Looking for last criterion of 7 in table –
Found in column “V”, row 13; return 6 values above/before criterion = 1
Looking for last criterion of 14 in table –
Found in column “P”, row 18; return 3 values above/before criterion = nothing, empty text(“”)
Hope you can help.
Thanks,
Sam
I am using Excel 2007 for Windows.
I have a table that consists of many columns and expanding rows; for this reason, I have referenced the table as a dynamic named range called “Data”. “Data” can contain duplicate values in both columns and rows. I have numeric labels in row 3, spanning the width of my table; “Data” starts in column “G”, row 4. Column “A” contains sequential numeric values (references) that can be used as relative row numbers for my table. The cells within the table are populated with a formula that returns either a numeric value or empty text (“”) showing a blank cell. Zero is a valid numeric value in the table.
Scenario:
Find the last instance of a specific numeric value (criterion) within “Data”.
From that instance, I would then like to return between 1 and more of the values located above/before the last instance of the specified criterion.
1. Find the last instance of a specific criterion within “Data”.
2. Based on last instance of the specific criterion, return the last 3 values above/before that last instance. The number of values to return will vary, and there may be no values to return.
3. Using dynamic name “Data” in a flexible formula based solution with an input cell for the criterion; input cell for quantity of values to return; error trap if fewer values available to be returned than requested; return the values across a single row.
Sample Layout:
Book5.xlsx attached
Expected Results:
Looking for last criterion of 0 (zero) in table –
Found in column “V”, row 14; return 1 value above/before criterion = 7
Looking for last criterion of 7 in table –
Found in column “V”, row 13; return 6 values above/before criterion = 1
Looking for last criterion of 14 in table –
Found in column “P”, row 18; return 3 values above/before criterion = nothing, empty text(“”)
Hope you can help.
Thanks,
Sam