Hello,
I have the following chart dynamic range that up until now was working perfectly because I had contiguous range of numbers (E3 in the example XL2BB):
=$E$3:INDEX($E$3:$S$3,COUNT($E$3:$S$3))
However, now I have rows that do not necessarily have contiguous numbers (row 6 in the example XL2BB), and this COUNT-based formula fails (E7 in the example XL2BB). So, I'm trying to adjust the formula such that it returns the correct range (highlighted on row 8 in example XL2BB) starting from E6 to the rightmost number in row 6 including all the blank cells in between (by the way, in my real data, the blanks in the ranges are formula-based blanks, not empty blanks).
Thanks for any input!
I have the following chart dynamic range that up until now was working perfectly because I had contiguous range of numbers (E3 in the example XL2BB):
=$E$3:INDEX($E$3:$S$3,COUNT($E$3:$S$3))
However, now I have rows that do not necessarily have contiguous numbers (row 6 in the example XL2BB), and this COUNT-based formula fails (E7 in the example XL2BB). So, I'm trying to adjust the formula such that it returns the correct range (highlighted on row 8 in example XL2BB) starting from E6 to the rightmost number in row 6 including all the blank cells in between (by the way, in my real data, the blanks in the ranges are formula-based blanks, not empty blanks).
Book1 | ||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
1 | ||||||||||||||||||||||
2 | Ex1 | contiguous numbers: | 3 | 6 | 4 | 8 | 11 | 45 | ||||||||||||||
3 | correct output | 3 | 6 | 4 | 8 | 11 | 45 | |||||||||||||||
4 | ||||||||||||||||||||||
5 | ||||||||||||||||||||||
6 | Ex2 | non-contiguous numbers: | 5 | 32 | ||||||||||||||||||
7 | incorrect output: | 5 | 0 | |||||||||||||||||||
8 | desired output: | 5 | 32 | |||||||||||||||||||
9 | ||||||||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E3:J3 | E3 | =$E$2:INDEX($E$2:$S$2,COUNT($E$2:$S$2)) |
E7:F7 | E7 | =$E$6:INDEX($E$6:$S$6,COUNT($E$6:$S$6)) |
Dynamic array formulas. |
Thanks for any input!
Last edited: