• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Fixing the Chart Range

M

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.
 

Attachments

  • Fixing the Chart Range.xlsm
    26.4 KB · Views: 3
If the labels in column A are always populated (not blank) then change your ETR named range definition to:
=OFFSET(ETRLabel,0,1)
 
You must log in or register to reply here.
Back
Top