I have an XY series i want to chart, as filtered by another column. This can be done with Autofilter. But, i do not want the sheet data itself to appear as filtered, as for other charts i do not want it filtered, and generally i want the sheet data to be fully visible, not filtered. Of course i could make a copy and then keep them in sync; kind of klunky, but maybe ok/doable.
I wonder if there is a way to filter a chart series in the same way you can filter with an array formula?
For instance the following array formula returns a correlation for Column E with Column F, filtered by Column M. I would like to see this as a scatter plot of D vs E, filtered in this way, without Autofilter on the sheet, i.e. analogous to the array formula. Anybody know a way to do this?
{=CORREL(IF(INDIRECT("data!M136:M"&TodayRow-1)="",INDIRECT("data!E136:E"&TodayRow-1)),IF(INDIRECT("data!M136:M"&TodayRow-1)="",INDIRECT("data!F136:F"&TodayRow-1)))}
Thanks!
I wonder if there is a way to filter a chart series in the same way you can filter with an array formula?
For instance the following array formula returns a correlation for Column E with Column F, filtered by Column M. I would like to see this as a scatter plot of D vs E, filtered in this way, without Autofilter on the sheet, i.e. analogous to the array formula. Anybody know a way to do this?
{=CORREL(IF(INDIRECT("data!M136:M"&TodayRow-1)="",INDIRECT("data!E136:E"&TodayRow-1)),IF(INDIRECT("data!M136:M"&TodayRow-1)="",INDIRECT("data!F136:F"&TodayRow-1)))}
Thanks!