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 column 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 table 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 multiple instances of a specific numeric value (criteria) within “Data”.
From that instance, I would then like to return between 1 and more of the non-blank values located below/after each instance of the specified criteria.
Sample Layout of table "Data":
Book9c.xlsx attached
Expected Results:
Looking for criterion of 0 (zero) in table –
Found in table row 1, column label 3; return value below/after criterion = 3
Found in table row 1, column label 5; return value below/after criterion = 3
Found in table row 1, column label 14; return value below/after criterion = 3
Found in table row 1, column label 22; return value below/after criterion = 6
Found in table row 6, column label 3; return value below/after criterion = Empty Text (“”)
Found in table row 11, column label 16; return value below/after criterion = 2
Found in table row 16, column label 1; return value below/after criterion = 4
Found in table row 16, column label 18; return value below/after criterion = 0
Found in table row 17, column label 9; return value below/after criterion = 7
Found in table row 17, column label 18; return value below/after criterion = 6
Found in table row 18, column label 22; return value below/after criterion = 11
Found in table row 19, column label 15; return value below/after criterion = 7
Found in table row 22, column label 1; return value below/after criterion = 6
Found in table row 25, column label 12; return value below/after criterion = 4
Found in table row 25, column label 15; return value below/after criterion = 1
Found in table row 26, column label 18; return value below/after criterion = Empty Text (“”)
Found in table row 27, column label 1; return value below/after criterion = Empty Text (“”)
Found in table row 27, column label 24; return value below/after criterion = Empty Text (“”)
Hope you can help.
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 column 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 table 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 multiple instances of a specific numeric value (criteria) within “Data”.
From that instance, I would then like to return between 1 and more of the non-blank values located below/after each instance of the specified criteria.
- By table row number and table column label find each instance of a specific criterion within “Data”.
- Based on each found instance of the specific criterion, return the value(s) below/after that instance. The number of values to return will vary, and there may be no values to return.
- Using dynamic name “Data” in a flexible formula based solution with an input cell for the criterion; error trap (return empty text “”) if no values available to be returned; return the values to separate cells across a single row.
Sample Layout of table "Data":
Book9c.xlsx attached
Expected Results:
Looking for criterion of 0 (zero) in table –
Found in table row 1, column label 3; return value below/after criterion = 3
Found in table row 1, column label 5; return value below/after criterion = 3
Found in table row 1, column label 14; return value below/after criterion = 3
Found in table row 1, column label 22; return value below/after criterion = 6
Found in table row 6, column label 3; return value below/after criterion = Empty Text (“”)
Found in table row 11, column label 16; return value below/after criterion = 2
Found in table row 16, column label 1; return value below/after criterion = 4
Found in table row 16, column label 18; return value below/after criterion = 0
Found in table row 17, column label 9; return value below/after criterion = 7
Found in table row 17, column label 18; return value below/after criterion = 6
Found in table row 18, column label 22; return value below/after criterion = 11
Found in table row 19, column label 15; return value below/after criterion = 7
Found in table row 22, column label 1; return value below/after criterion = 6
Found in table row 25, column label 12; return value below/after criterion = 4
Found in table row 25, column label 15; return value below/after criterion = 1
Found in table row 26, column label 18; return value below/after criterion = Empty Text (“”)
Found in table row 27, column label 1; return value below/after criterion = Empty Text (“”)
Found in table row 27, column label 24; return value below/after criterion = Empty Text (“”)
Hope you can help.