+ Reply to Thread
Results 1 to 6 of 6

Hidden and Empty Cell Settings

  1. #1
    Registered User
    Join Date
    04-19-2020
    Location
    The Netherlands
    MS-Off Ver
    2016
    Posts
    12

    Exclamation Hidden and Empty Cell Settings

    Hello,

    I have the following problem. I have this line chart. Some dates in column have zero values, the line chart shows them as zero. But I do not want that. When I select Select Data in the Chart Setting and choose Hidden and Empty Cells setting and choose Connect Data points with line the chart does not change.
    What I am doing wrong?

    Greetings,

    Martin van Koningsbruggen
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Hidden and Empty Cell Settings

    Add a helper column for the dates and use a formula to show error if there is no associated error. Then use the helper column instead of the actual column
    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
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    696

    Re: Hidden and Empty Cell Settings

    Try this:

    Extract the dates if the value of branch is greater than zero.
    =IFERROR(INDEX($B$2:$B$31,MATCH(0,IF($C$2:$C$31>0,COUNTIF($G$1:G1,$B$2:$B$31),""),0)),"")

    Then use offset function to name the range.
    =OFFSET(Forecast!$G$2,,,COUNTA(Forecast!$G$2:$G$32)-COUNTBLANK(Forecast!$G$2:$G$32))

    Name the branch range as well:
    =OFFSET(Forecast!$H$2,,,COUNTA(Forecast!$G$2:$G$32)-COUNTBLANK(Forecast!$G$2:$G$32))

    Right click on the chart and change the last part after "!" mark.
    ='Benelux Daily October Test.xlsm'!Branch
    Also change the last part of date
    ='Benelux Daily October Test.xlsm'!Date
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-19-2020
    Location
    The Netherlands
    MS-Off Ver
    2016
    Posts
    12

    Re: Hidden and Empty Cell Settings

    Quote Originally Posted by Shareez Saleem View Post
    Try this:

    Extract the dates if the value of branch is greater than zero.
    =IFERROR(INDEX($B$2:$B$31,MATCH(0,IF($C$2:$C$31>0,COUNTIF($G$1:G1,$B$2:$B$31),""),0)),"")

    Then use offset function to name the range.
    =OFFSET(Forecast!$G$2,,,COUNTA(Forecast!$G$2:$G$32)-COUNTBLANK(Forecast!$G$2:$G$32))

    Name the branch range as well:
    =OFFSET(Forecast!$H$2,,,COUNTA(Forecast!$G$2:$G$32)-COUNTBLANK(Forecast!$G$2:$G$32))

    Right click on the chart and change the last part after "!" mark.
    ='Benelux Daily October Test.xlsm'!Branch
    Also change the last part of date
    ='Benelux Daily October Test.xlsm'!Date
    Hi Shareez Saleem,

    Thank you for your answer. Quite impressive. You mention the 2 OFFSET functions but they are not in the sheet you updated. Despite that the formulas in column G to J are working.
    When I copied the functions from G to J to my working sheet they show no result despite correcting it to the correct fields. I also noticed that your formula below starts and ends with {}. When I copied the formula to my sheet they were not copied when I add them the formula is not correct anymore.

    {=IFERROR(INDEX($B$2:$B$31,MATCH(0,IF($C$2:$C$31>0,COUNTIF($G$1:G1,$B$2:$B$31),""),0)),"")}


    with 5MB my working sheet is too big to attach. I am not an expert like you so I hope you can help me to understand the challenges.

    With kind regards,

    Martin

  5. #5
    Valued Forum Contributor
    Join Date
    04-27-2015
    Location
    Abu Dhabi, U.A.E
    MS-Off Ver
    Office 365 | 2016
    Posts
    696

    Re: Hidden and Empty Cell Settings

    {} means array formula, but what I have provided is a normal formula (without "{" )
    Regarding the offset function used, please go to the 'Formula' tab in the ribbon, go click on 'Name Manager' and review 3 named ranged which we have created using Offset function.

    Period
    =OFFSET(Forecast!$G$2,,,COUNTA(Forecast!$G$2:$G$32)-COUNTBLANK(Forecast!$G$2:$G$32))
    Branch
    =OFFSET(Forecast!$H$2,,,COUNTA(Forecast!$G$2:$G$32)-COUNTBLANK(Forecast!$G$2:$G$32))
    Export
    =OFFSET(Forecast!$I$2,,,COUNTA(Forecast!$G$2:$G$32)-COUNTBLANK(Forecast!$G$2:$G$32))
    Import
    =OFFSET(Forecast!$J$2,,,COUNTA(Forecast!$G$2:$G$32)-COUNTBLANK(Forecast!$G$2:$G$32))

    Make sure the sheet name is Forecast, if there is any change in sheet name, please change it accordingly.

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

    Re: Hidden and Empty Cell Settings

    Depending on how you want to treat those zero data points and your dates the formula for the helper column could be as simple as
    =IF(C2=0,NA(),C2)

    If you want all dates shown with the interpolated between points just change the Hidden/Empty cells setting to Connecting line.
    Cheers
    Andy
    www.andypope.info

+ 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: 08-30-2020, 02:07 AM
  2. Value in cell is empty when a column is hidden
    By kampatel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-23-2016, 01:21 PM
  3. Replies: 1
    Last Post: 02-11-2015, 07:47 AM
  4. [SOLVED] hidden and empty cells settings
    By ivansamsonov in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-31-2013, 10:36 AM
  5. [SOLVED] Cell settings - value with hidden apostrophe
    By sine.grujica in forum Excel General
    Replies: 3
    Last Post: 06-19-2013, 04:24 AM
  6. Hidden and Empty Cells Grayed Out
    By Potatosalad2 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-09-2008, 03:45 AM
  7. How can I remove hidden empty text cells from Excel?
    By Bill Z1P in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-02-2005, 03:04 PM

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