+ Reply to Thread
Results 1 to 3 of 3

Dynamic Chart to Exclude Blank Cells (with formulas)

  1. #1
    Forum Contributor
    Join Date
    08-08-2015
    Location
    NJ
    MS-Off Ver
    2013
    Posts
    205

    Dynamic Chart to Exclude Blank Cells (with formulas)

    Hello, am trying to set up a chart based on dynamic data from a table that contains formulas, which includes blank cells. Is there a way to dynamically make this chart work despite the formulas, so that cells with no values do not appear in the chart?

    The attached shows a sample of a chart, showing the values, both with and without data. I attempted to use an offset formula but the blank cells with formulas are still showing. Thanks for any input, appreciate any assistance.

    Sheryl
    Attached Files Attached Files

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Dynamic Chart to Exclude Blank Cells (with formulas)

    modify your named ranges

    color :=OFFSET(Sheet1!$R$5,0,0,SUM(IF(LEN(Sheet1!$R$5:$R$12)>0,1,0)),1)
    one: =OFFSET(color,0,1)
    Cheers
    Andy
    www.andypope.info

  3. #3
    Forum Contributor
    Join Date
    08-08-2015
    Location
    NJ
    MS-Off Ver
    2013
    Posts
    205

    Re: Dynamic Chart to Exclude Blank Cells (with formulas)

    Thank you !!!
    I am unable to close this post on my phone so will close and leave a reputation once I have access to my PC. Thanks for the quick response.
    Sheryl

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Pivot Table / Chart Ignore / Exclude blank "" cells created using IF formula
    By randomreflex in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 05-23-2018, 08:59 AM
  2. Dynamic Print Area that ignores blank cells containing formulas
    By ms.mount in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 07-03-2017, 05:46 PM
  3. [SOLVED] Dynamic Chart ranges are picking the blank cells as these contain formula
    By controlfreak in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-28-2014, 04:15 AM
  4. Dynamic Ranges - Ignoring Blank Cells with Underlying Formulas
    By jmm722 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-25-2014, 04:14 PM
  5. Dynamic Ranges vs. Formulas that exclude blanks?
    By amartino44 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-15-2013, 02:04 PM
  6. Dynamic Chart will not work as empty cells contain Formulas
    By feno1 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-11-2011, 03:22 AM
  7. Exclude Blank Cells from chart legend
    By jamphan in forum Excel General
    Replies: 1
    Last Post: 10-19-2010, 05:25 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1