+ Reply to Thread
Results 1 to 8 of 8

Help! Dynamic horizontal chart not having dynamic labels whilst i followed all instruction

  1. #1
    Registered User
    Join Date
    12-22-2014
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    44

    Help! Dynamic horizontal chart not having dynamic labels whilst i followed all instruction

    Hi There,

    All my labels are set up but it is stil not working i have followed the instructions of post: excel-charting-and-pivots/655282-dynamic-chart-with-horizontal-table
    I have made all my labels. and i have added Two of those labels inside a Graph the WK notation is working dynamicly but the Range VRDLBL1 VRDLBL2 are not working dynamicly

    I have changed the COUNTA formula to a COUNT IF >0 because it was counting my formula's as well.

    Attached the file hope someone could help me
    im overlooking something

    The Tab Voorraad is where the graph is.

    Sincere greetings.

    Aaron
    Attached Files Attached Files

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,459

    Re: Help! Dynamic horizontal chart not having dynamic labels whilst i followed all instruc

    Hi there,

    I've changed the formulas in the highlighted cells (rows 1 & 2) of the "Voorraad" worksheet so that #N/A is displayed instead of Zero when no data exist for the week in question - this means that weeks for which no data exist are not included in the graph. Is the graph in the attached version of your workbook what you need?

    Hope this helps.

    Regards,

    Greg M
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    12-22-2014
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    44
    Quote Originally Posted by Greg M View Post
    Hi there,

    I've changed the formulas in the highlighted cells (rows 1 & 2) of the "Voorraad" worksheet so that #N/A is displayed instead of Zero when no data exist for the week in question - this means that weeks for which no data exist are not included in the graph. Is the graph in the attached version of your workbook what you need?

    Hope this helps.

    Regards,

    Greg M

    Thanks man I will try this at work on Monday and I will reply again ! Funny if this was so simple :p

  4. #4
    Registered User
    Join Date
    12-22-2014
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    44

    Re: Help! Dynamic horizontal chart not having dynamic labels whilst i followed all instruc

    Hey man,

    No i will change the Graph.
    But this was what i was looking for!
    So this was what i was missing? the need for displaying N/B?

    greetings.

    Aaron

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,459

    Re: Help! Dynamic horizontal chart not having dynamic labels whilst i followed all instruc

    Hi again,

    Yes - a graph will display a zero value, which is what the formula produces in a cell for which no data is available - those cells DO contain zeroes even though the NumberFormat property of those cells is such that a blank value is displayed.

    A graph will not display a #N/A value (which is what the new version of the formula produces).

    Hope this helps,

    Regards,

    Greg M

  6. #6
    Registered User
    Join Date
    12-22-2014
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    44

    Re: Help! Dynamic horizontal chart not having dynamic labels whilst i followed all instruc

    Hi again Greg,

    Have you seen that i have made labels that have offset functions and that the graph is pointing to those labels?

    =OFFSET(Voorraad!$B$2,0,0,1,MAX(IF(ISNA(Voorraad!$B$2:$BA$2),0,IF(Voorraad!$B$2:$BA$2<>"",COLUMN(Voorraad!$B$2:$BA$2),0)))-1)
    =OFFSET(Voorraad!$B$3,0,0,1,MAX(IF(ISNA(Voorraad!$B$3:$BA$3),0,IF(Voorraad!$B$3:$BA$3<>"",COLUMN(Voorraad!$B$3:$BA$3),0)))-1)

    and that this label looks at those two?
    =OFFSET(Voorraad!$B$1,0,0,1,MAX(COUNTIF(VRDLBL1,">0"),COUNTIF(VRDLBL2,">0")))

    i made this because i was following the other post and have downloaded that workbook that the guy made cause i wanted to do the same thing
    the N/B is kinda ugly. mind helping me with the label function?

  7. #7
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,459

    Re: Help! Dynamic horizontal chart not having dynamic labels whilst i followed all instruc

    Hi again,

    See the attached workbook.

    I've created three new dynamic Named Ranges "ptrGrondstoffen", "ptrHandelswaren" and "ptrWeekLabels", and used them to populate a new Chart.

    The formulas for the first two Named Ranges are similar (but not identical) to your own, i.e.

    Please Login or Register  to view this content.
    The formula for the third Named Range is essentially the same as the one you've used.



    The formulas in the first two rows of the "Voorraad" worksheet are:

    Please Login or Register  to view this content.
    copied across and down from Cell B2.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files
    Last edited by Greg M; 05-07-2019 at 09:25 AM. Reason: Latest workbook attached

  8. #8
    Registered User
    Join Date
    12-22-2014
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    44

    Re: Help! Dynamic horizontal chart not having dynamic labels whilst i followed all instruc

    Hey Greg,

    This is exactly the solution i was looking for !.
    Your formula seems way more cleaned up than my does.
    I can now conclude from your formulate that.

    The offset is triggered by : if a number is anything bigger or smaller than 0 the range will increase or decrease in size.
    the WK label looks at those labels and will increase or decrease in size.

    Thanks alot man!

+ 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 Pie Chart Labels
    By HarmenP in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-15-2019, 09:36 AM
  2. [SOLVED] Dynamic Chart with Horizontal Data
    By majime01 in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 10-05-2018, 07:33 PM
  3. [SOLVED] Dynamic chart with last 11 horizontal values
    By scaffdog845 in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 04-07-2015, 11:04 AM
  4. Dynamic Horizontal + Vertical chart
    By kluchy in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-13-2013, 07:59 AM
  5. Dynamic Chart with Horizontal Table
    By timarcarze in forum Excel Charting & Pivots
    Replies: 18
    Last Post: 12-20-2012, 11:35 AM
  6. Horizontal Bar Chart - Dynamic?
    By theghost in forum Excel Charting & Pivots
    Replies: 13
    Last Post: 09-24-2009, 02:41 AM
  7. Dynamic chart labels
    By dano in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-22-2005, 08:05 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