+ Reply to Thread
Results 1 to 4 of 4

Skipping cells that have formula returning blank in a plot

  1. #1
    Registered User
    Join Date
    02-22-2014
    Location
    London, England
    MS-Off Ver
    Excel 2016
    Posts
    16

    Skipping cells that have formula returning blank in a plot

    Excel 2016

    I am trying to plot a chart that finds the end of populated cells.
    The problem I am facing is that since all my cells (SETUP.chartTOPICS columns A and B) are all populated with a formula to update when Database column E is updated.
    I created a chart using a definedName using offset so it also continues to update.
    You see the resulting plot has a lot of empty entries, I'd like it to dynamically change the range based on the column entries.

    Is this possible?
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,075

    Re: Skipping cells that have formula returning blank in a plot

    Structure the formulas so they return an error instead if a blank. Charts ignore error cells.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-22-2014
    Location
    London, England
    MS-Off Ver
    Excel 2016
    Posts
    16

    Re: Skipping cells that have formula returning blank in a plot

    Quote Originally Posted by FDibbins View Post
    Structure the formulas so they return an error instead if a blank. Charts ignore error cells.
    I tried using NA() in the value if false entry, but my plot still includes #N/A.
    Can you provide an example?

  4. #4
    Registered User
    Join Date
    02-22-2014
    Location
    London, England
    MS-Off Ver
    Excel 2016
    Posts
    16

    Re: Skipping cells that have formula returning blank in a plot

    Quote Originally Posted by kryssie.e View Post
    I tried using NA() in the value if false entry, but my plot still includes #N/A.
    Can you provide an example?

    I changed the named range definition and now it works.

    FROM: =OFFSET(SETUP.chartTOPICS!$A$4,0,0,COUNTA(SETUP.chartTOPICS!$A$4:$A$1000))
    TO: =OFFSET(SETUP.chartTOPICS!$A$4,0,0,COUNTIF(SETUP.chartTOPICS!$A$4:$A$1000,"<>#N/A"))

+ 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. Replies: 3
    Last Post: 02-07-2018, 04:43 PM
  2. [SOLVED] Skipping dates have blank values and plot remaining dates.
    By Sekars in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-31-2016, 11:15 AM
  3. [SOLVED] LookUp skipping empty cells and returning first entry
    By Colin Smit in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-28-2015, 05:51 AM
  4. Replies: 0
    Last Post: 03-05-2013, 09:22 AM
  5. Skipping Blank cells in formula(searched but cant figure how to apply)
    By scotinexcile in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 08-21-2012, 09:14 PM
  6. Excel 2007 : iferror(index Formula returning blank cells
    By Martin Chamberlin in forum Excel General
    Replies: 7
    Last Post: 11-15-2011, 08:45 AM
  7. Skipping blank cells in simple math formula
    By jimtmcdaniels in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-17-2006, 01:40 AM

Tags for this Thread

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