msharma864512
Member
Hi,
I have a report wherein the chart range always varies, sometime we have less entries, sometimes there are more entries.
However I want to have a dynamic chart which will have the range selected for the entire range. Hence the chart will be created only for the cells where we have the data.
The issue I am facing is If I have any blank cell in between the range, then the formula is not giving the current results.
For e.g. in the attached spreadsheet the data range selected is B3:B21, however as we have blank cells in cell B6 and B7, hence the chart is selecting the range only till B19,
if I delete value from one more cell, then the range selection will only be till B18.
This is the formula I have used: =OFFSET(Sheet1!$B$3,0,0,COUNT(Sheet1!$B$3:$B$21),1)
Please help me so that If i have selected any range it show the entire data set even if there is any blank cell in between.
I have a report wherein the chart range always varies, sometime we have less entries, sometimes there are more entries.
However I want to have a dynamic chart which will have the range selected for the entire range. Hence the chart will be created only for the cells where we have the data.
The issue I am facing is If I have any blank cell in between the range, then the formula is not giving the current results.
For e.g. in the attached spreadsheet the data range selected is B3:B21, however as we have blank cells in cell B6 and B7, hence the chart is selecting the range only till B19,
if I delete value from one more cell, then the range selection will only be till B18.
This is the formula I have used: =OFFSET(Sheet1!$B$3,0,0,COUNT(Sheet1!$B$3:$B$21),1)
Please help me so that If i have selected any range it show the entire data set even if there is any blank cell in between.