Forum Discussion
Automate chart data selection
You may add two named ranges
Xrange: =INDEX(Sheet1!$A:$A,AGGREGATE(15,6,1/(Sheet1!$B$2:$B$32<>0)*ROW(Sheet1!$B$2:$B$32),1)-1):INDEX(Sheet1!$A:$A,AGGREGATE(14,6,1/(Sheet1!$B$2:$B$32<>0)*ROW(Sheet1!$B$2:$B$32),1)+1) Yrange =INDEX(Sheet1!$B:$B,AGGREGATE(15,6,1/(Sheet1!$B$2:$B$32<>0)*ROW(Sheet1!$B$2:$B$32),1)-1):INDEX(Sheet1!$B:$B,AGGREGATE(14,6,1/(Sheet1!$B$2:$B$32<>0)*ROW(Sheet1!$B$2:$B$32),1)+1)and add use them for chart data
Xrange
Yrange (Series):
scheij I can't change the automatic function of the select data function but here is a pair of formulas you can paste in a new pair of columns that will 'pull' that range of data for you. I used a Defined Name "dRange" to define where your data (y-data) is located. You can either create that NAME in the Name Manager or replace each occurrence with the range you need:
=IFERROR(IF(ROW()=1,"New X",INDEX(OFFSET(dRange,MATCH(TRUE,dRange>0,0)-2,-1,MATCH(TRUE,dRange>0)-MATCH(TRUE,dRange>0,0)+3),COUNT(OFFSET($A$1,0,COLUMN()-1,ROW()-1))+1)),"")=IFERROR(IF(ROW()=1,"New Y",INDEX(OFFSET(dRange,MATCH(TRUE,dRange>0,0)-2,0,MATCH(TRUE,dRange>0)-MATCH(TRUE,dRange>0,0)+3),COUNT(OFFSET($A$1,0,COLUMN()-1,ROW()-1))+1)),"")If placed starting in Row 1 it will add the "New X" and "New Y" titles.
NOTE: these are both ARRAY formulas and you must hit CTRL-SHIFT-ENTER after you paste the text in
mtarler This remarkable solution achieves much of what I need, but the array formulas seem to "run out of steam" and fail to detect non-zero values in subsequent rows (highlighted in attached workbook). Changing B20 to a number > 0 extends the array formula results to the next zero value, but not to the last zero as it should. Thank you for your help.
