• 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.

Preventing Blank Columns from Displaying

Hello,

In the attached file, I have a report that displays sparklines in column D, and graphs in columns W-AE off to the right. The sparklines reference columns AJ-AU. Columns AN-AU have zeros in them. They'll be filled in as the year progresses. Similarly, the graphs reference the columns for Jul-Oct 2019. If I reference the dates to also include Nov 2019-June 2020, the graph series will be short; most of the graphs will be blank (using na() for zeros).

I would like to have both sparklines and graphs display only the dates for which I have actuals. I don't see how to do this without a lot of manual adjustments, and I've got 75 of these to generate every month.

Dynamic ranges might be useful here. As I add data to the range, the range would change automatically to pick it up. But what if my graph ranges are already set up for the whole year? With sparklines, the dynamic ranges work if the next column (in my example attached, Nov-19) is blank. Nov-19 could have "", or #NA, but it will never be blank—unless I manually add data for Nov-19 and fill in the vacant column. Sparklines interpret any of these as zero and will display zeroes for the remaining months, leaving extra space in the sparkline where I don't want it.

Each month, I'll import the month's actuals into the file through Power Query, where formulas will then pick it up. Since I'm not adding data manually to the tables, the sparkline range has to be predefined as the whole year, but that leaves extra space in the sparkline cell.

What are some options?

Paul
 

Attachments

  • Monitoring Report Chandoo.xlsb
    494.3 KB · Views: 2
Hi,
See attached. Look for the added entries in the name manager for the dynamic sparkline ranges.
You can reapply for your charts.
 

Attachments

  • Copy of Monitoring Report Chandoo.xlsb
    493.5 KB · Views: 4
Thank you! I was able to follow along with how you did this for the sparklines. However, I don't understand how to apply it to graphs. Can you show me in the file I attached?
 
Thanks, Guido. I find this very difficult to understand and work with, even after reading blog posts about the OFFSET function. While I was able to reproduce what you'd done with the sparklines, I couldn't reproduce what you did with the charts. I tried my best to reproduce and modify what you did for application to another file, but no luck. Excel wouldn't let me get as far as even changing the series. I even copied your modified chart into my file and changed the filename and sheet name, using the same names I'd set up with the Name Manager. I cannot figure out what's the problem. Perhaps there's a format issue. But I'll keep trying.
Paul
 
Back
Top