+ Reply to Thread
Results 1 to 6 of 6

Dynamic X Axis for Bar Chart

  1. #1
    Registered User
    Join Date
    10-01-2013
    Location
    USA
    MS-Off Ver
    2007
    Posts
    30

    Dynamic X Axis for Bar Chart

    Column A is a list of names populated from an array formula. The result of the array formula may yield anywhere from two to twenty names along with the data associated with the names.
    Column B is a value
    Column C is a value

    Chart1 has an X axis of Column A

    The Y axis is numeric values

    Since Column A can have a wide range of names, how can I make the X axis dynamic so I don't have a total of 20 place holders when there are only 2 or 3 records?

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,066

    Re: Dynamic X Axis for Bar Chart

    Just like we did in your other thread, you need to create some dynamic named ranges. This might be a good start.

    https://peltiertech.com/Excel/Charts/DynamicCharts.html

    If you have troubles with this, attach some sample data and we can show you how.
    Last edited by jeffreybrown; 02-07-2020 at 07:13 PM.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    10-01-2013
    Location
    USA
    MS-Off Ver
    2007
    Posts
    30

    Re: Dynamic X Axis for Bar Chart

    Thank you for the link! This helped me understand your solution to my last problem. I did try variations of the OFFSET function to see if I could do the same thing with the current problem but I continue to get errors. Maybe due to the dynamic range having a fixed number of rows. I've attached a more complex sample file that better reflects the challenge.

    On worksheet TUI, "Chart 2" has an X axis with the names in B24:B43 (20 rows, rangename='Technician'). Those 20 rows have an array formula in column B so COUNTA for that range will always produce "20". However, I can't even get the formula to work to produce 20 X axis spaces so I'm obviously doing something wrong.

    This formula in the X axis range produces "That Function is not valid"
    =OFFSET(TUI!$B$24,-1,0,COUNTA(TUI!$B$24:$B$43)-COUNTBLANK(TUI!$B$24:$B$43),1)

    I also tried using the range name with the same results. So I resorted to working with the actual cell references until I got it ironed out.

    Thank you for your help!
    Attached Files Attached Files
    Last edited by AliGW; 02-11-2020 at 09:47 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,066

    Re: Dynamic X Axis for Bar Chart

    See if this works out for you.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-01-2013
    Location
    USA
    MS-Off Ver
    2007
    Posts
    30

    Re: Dynamic X Axis for Bar Chart

    That worked! So it was as simple as putting the filename before the rangename?
    Last edited by AliGW; 02-11-2020 at 09:47 AM. Reason: Please don't quote unnecessarily!

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,066

    Re: Dynamic X Axis for Bar Chart

    Glad it worked out for you and you're welcome.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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. Dynamic Chart on both X and Y axis with VBA.
    By Pinkoto in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-28-2017, 05:08 PM
  2. Dynamic Chart on both X and Y axis
    By Pinkoto in forum Excel Charting & Pivots
    Replies: 18
    Last Post: 08-26-2017, 04:09 PM
  3. Dynamic Axis in chart
    By musicbox1970 in forum Excel General
    Replies: 3
    Last Post: 03-03-2017, 07:02 AM
  4. [SOLVED] Dynamic x-axis chart
    By NeoFlex in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 04-02-2013, 09:50 AM
  5. Legend Only Shows Used Axis on Dynamic Chart (X, Y and Y2 axis)
    By TEBrown in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 06-21-2012, 10:15 AM
  6. Dynamic X Axis values of Chart
    By ronanm in forum Excel General
    Replies: 1
    Last Post: 12-09-2010, 12:35 PM
  7. chart with dynamic x axis
    By name in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-04-2006, 10:45 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1