+ Reply to Thread
Results 1 to 15 of 15

Chart ignore non filled in columns

  1. #1
    Registered User
    Join Date
    03-01-2018
    Location
    Chicago
    MS-Off Ver
    Office Professional 2016
    Posts
    16

    Chart ignore non filled in columns

    Hi,

    I am trying to create a chart which will exclude all non filled in columns. I am trying to create something fool proof, so I have all the 52 weeks of the year already created, it is just still blank.

    I am trying to make the X axis pull data only until the data is filled.

    Do I have to go down the macro path?

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Chart ignore non filled in columns

    Your explanation is a bit vague.... Try this out, though. Happy to explain as required.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    03-01-2018
    Location
    Chicago
    MS-Off Ver
    Office Professional 2016
    Posts
    16

    Re: Chart ignore non filled in columns

    Thank you Glenn,

    How would you do it the other way around. This is exactly what I need, but instead of the information is not filled in vertically, it is not filled in yet horizontally.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Chart ignore non filled in columns

    Try this. Your description WAS too vague!!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-01-2018
    Location
    Chicago
    MS-Off Ver
    Office Professional 2016
    Posts
    16

    Re: Chart ignore non filled in columns

    Thank you Glenn, it might have been vague, but I was not sure what I was looking for.

    This is great, although I am not sure if there is a solution if my reference table is feeding from an other table. I have functions in the reference table, resulting in "" (of course it can be 0 as well). Therefore It is finally solving my problem of being dynamic, but still trying to show all the 0s or empty cells.

    Also, I have more than 4 rows of information. Could you help me out how to add the rest of them in a quick way?

    I appreciate your help and time.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Chart ignore non filled in columns

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

  7. #7
    Registered User
    Join Date
    03-01-2018
    Location
    Chicago
    MS-Off Ver
    Office Professional 2016
    Posts
    16

    Re: Chart ignore non filled in columns

    Attached. Thank you.
    Attached Files Attached Files

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Chart ignore non filled in columns

    More vagueness.... I had hoped that you might have posted a file with raw data, your formula all in place and an attempt to create the dynamic chart. But... Now you want me to guess how your layout looks, create the data table from my imagination, create a graph.. again, create the named ranges... again.

    No. that's not how it works, I don't play guessing games forever. I have modified my file, to incorporate a formula in the data table and to train the functionality. Try to implement it yourself. YOU know what your data layout should be. I don't.

    I'm more than happy to help further if/when you hit a snag... but, for now, over to you to try to implement this.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-01-2018
    Location
    Chicago
    MS-Off Ver
    Office Professional 2016
    Posts
    16

    Re: Chart ignore non filled in columns

    I hope this help. Appreciate the help.

    Trying to create the chart based on the bottom table. Raw data, and all the functions are in there.
    Attached Files Attached Files

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Chart ignore non filled in columns

    I have done areas 1 - 3. Firstly, add the remaining named ranges (CTRL-F3) for the other areas. Then add the remaining datasets to the chart chart tools/design/add data usin variants of the formulae already in place.

    my only remaining concern is that, with 15 areas, it could look rather messy....
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    03-01-2018
    Location
    Chicago
    MS-Off Ver
    Office Professional 2016
    Posts
    16

    Re: Chart ignore non filled in columns

    I have two further questions to you Glenn,

    1. when you add the named are with (CTRL+F3) how do you add the formula and the naming quickly. so I dont have to retype everything.

    2. How do you add them quickly based on the naming to the chart? The series values, it is based on the named range. I would type it out by hand and I suspect there is a more elegant way.

    Thank you!

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Chart ignore non filled in columns

    I wrote area1 out and then copy pasted into area 2, manually adjusting the rows. Same for the additional datasets. Takes maybe 15 seconds per named range, a bit less for each dataset.

  13. #13
    Registered User
    Join Date
    03-01-2018
    Location
    Chicago
    MS-Off Ver
    Office Professional 2016
    Posts
    16

    Re: Chart ignore non filled in columns

    Glenn, this is strange. I wrote the named areas, and build the chart step by step as well. And for some strange reason, it is still capturing my 0s on the X axis. Do I need to change some settings? MY master file looks the exact same.

  14. #14
    Registered User
    Join Date
    03-01-2018
    Location
    Chicago
    MS-Off Ver
    Office Professional 2016
    Posts
    16

    Re: Chart ignore non filled in columns

    It counts the empties in row 50. None of them are empty. The master file I have and the file I shared looks the exact same. I dont understand

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: Chart ignore non filled in columns

    Im away out for a couple of hours. You'd need to post your sheetas I can't see what I can't see....

+ 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: 5
    Last Post: 10-06-2017, 03:59 PM
  2. Replies: 13
    Last Post: 08-19-2016, 03:50 PM
  3. [SOLVED] .Find Help - Search only in specific columns (or ignore other columns)?
    By NewYears1978 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-25-2014, 11:32 AM
  4. Replies: 6
    Last Post: 07-02-2012, 12:20 AM
  5. How can one ignore all spaces to shift filled cells to the left of the worksheet
    By Mumbaikar in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-10-2012, 07:33 AM
  6. Filled Area, column chart overlay
    By firefly2k8 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-14-2009, 06:49 AM
  7. Filled radar chart with multiple series
    By MSP77079 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-02-2007, 08:43 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