+ Reply to Thread
Results 1 to 6 of 6

How can I dynamically change the format of the Y axis based on the source data

  1. #1
    Registered User
    Join Date
    09-11-2007
    Posts
    21

    How can I dynamically change the format of the Y axis based on the source data

    Hello,

    Not sure how to best explain this but here goes. I have a set of data with multiple measures (i.e. gross profit $, Product Cost $, Product Cost %, etc.). I am charting on this data but using a drop down to select which measure to chart on. That data then gets populated in specific cells in which the chart get's it's values. The data in these cells are formatted based on conditional formatting. For instance if I choose Gross Profit $ from the drop down, the cells will populate with the gross profit and be formatted as Accounting no decimals. However if I choose Product Cost % from the drop down the values change formatting to a % with two decimals. My goal here is to have the format on the chart's y axis change based on the cells formatting.

    Attached is a sample file.

    Your help is much appreciated!

    Randy
    Attached Files Attached Files
    Last edited by rmeister29; 01-22-2015 at 10:55 AM. Reason: Mark Solved

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

    Re: How can I dynamically change the format of the Y axis based on the source data

    You need to use a Named range as the data series source. This way the number format related to the cell will be picked up.

    Use Formulas > Name Manager, to see named range MYDATA
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    09-11-2007
    Posts
    21

    Re: How can I dynamically change the format of the Y axis based on the source data

    Andy, Thanks for your help! That worked perfectly.

  4. #4
    Registered User
    Join Date
    11-30-2022
    Location
    Georgia USA
    MS-Off Ver
    Office 2022
    Posts
    1

    Re: How can I dynamically change the format of the Y axis based on the source data

    Thanks Andy. I had the same problem and your solution solved it! Awesome!

  5. #5
    Registered User
    Join Date
    02-05-2018
    Location
    Netherlands
    MS-Off Ver
    office365
    Posts
    1

    Re: How can I dynamically change the format of the Y axis based on the source data

    Hello Andy, I've been looking for this solution for years! Yet I do not fully how the y-axis can change in format automatically and how it would be if you have two variables charted. Could you still show another screenshot or two? Many thanks in advance.

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

    Re: How can I dynamically change the format of the Y axis based on the source data

    Giel, Start a new thread for you problem.
    Attach example workbook to illustrate what you have, what you have and what the desired output is

+ 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. [SOLVED] Dynamically change X-axis as per dropdown selection
    By Sai Prashanth in forum Excel General
    Replies: 3
    Last Post: 09-12-2014, 05:20 PM
  2. Change the range in x axis dynamically for line chart
    By dimwit in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-27-2013, 05:23 AM
  3. Y Axis data source changes based on the Named range listed in Dropdown
    By aravindhan_31 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 11-09-2012, 12:43 AM
  4. Get data from source workbook and paste to destination in change format
    By farrukh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-03-2012, 01:47 AM
  5. Dynamically change name of source data in VLOOKUP
    By anayeri in forum Excel General
    Replies: 8
    Last Post: 11-03-2009, 12:41 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