+ Reply to Thread
Results 1 to 5 of 5

Get rid of blank data entry

  1. #1
    Registered User
    Join Date
    08-06-2008
    Location
    East Ayrshire
    MS-Off Ver
    2308
    Posts
    37

    Get rid of blank data entry

    I have made up a chart comparing two past years with the coming year. The past years obviously have entries in all data cells but the coming year doesn't. However, the coming year is taking its data from another sheet so there are formulas in the corresponding data source sheet cells. The formulas are like the following:

    =IF('Sales 2019'!E11=0,NA(),'Sales 2019'!E11)

    I did this as all the documentation online states that if the entry comes out as #N/A then it is ignored on the chart. However, as you can see from the attached, where there is no actual data (just the formula) in the cell then it is coming out as a flat line on the chart. I simply want to get rid of that flat line for any data that is not yet inputted.

    Is there a way of doing this?

    Chart_data_0.jpg

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Get rid of blank data entry

    Change to an XY scatter - then the NA()s will be properly handled.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    08-06-2008
    Location
    East Ayrshire
    MS-Off Ver
    2308
    Posts
    37

    Re: Get rid of blank data entry

    tried that and the same outcome I'm afraid

    Screenshot_1.jpg

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Get rid of blank data entry

    Works for me - Capture.JPG

  5. #5
    Registered User
    Join Date
    08-06-2008
    Location
    East Ayrshire
    MS-Off Ver
    2308
    Posts
    37

    Re: Get rid of blank data entry

    I am an idiot!! I had the formula to show #N/A as being if the cell =0 but it should have been =""

    Thank you - you helped point me in the right direction - many thanks
    Last edited by nikwak; 06-13-2018 at 02:13 PM.

+ 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: 6
    Last Post: 02-06-2017, 09:46 PM
  2. [SOLVED] Record Date of Data Entry Except When Blank
    By majime01 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 03-07-2016, 05:59 PM
  3. [SOLVED] IF/AND Correct Score Predictor - Blank Before Data Entry?
    By johnmcluckie23 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-22-2016, 06:10 AM
  4. Cancel Data Entry if text box is blank
    By SeanBaird in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-10-2015, 08:39 PM
  5. Replies: 2
    Last Post: 09-18-2008, 05:47 AM
  6. [SOLVED] data validation drop-down menu blank entry
    By crapit in forum Excel General
    Replies: 2
    Last Post: 12-14-2005, 09:25 AM
  7. [SOLVED] data entry to next blank TextBox
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-17-2005, 06:05 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1