+ Reply to Thread
Results 1 to 2 of 2

A static-dynamic bar chart - is it possible?

  1. #1
    Registered User
    Join Date
    03-28-2020
    Location
    Poland
    MS-Off Ver
    2016
    Posts
    1

    Question A static-dynamic bar chart - is it possible?

    chart.jpg

    Hello,

    while working on a report automation i ran up against a problem that have so far seemed unsolveable.

    I have a bar chart which in all other conditions would have been possible to be made dynamic with the usual OFFSET + COUNT/COUNTA (it is updated weekly and should display data for the 9 last weeks only), but there's one parameter that must maintain a fixed reference. It's marked with red on the diagram. I would like to keep the other 9 bars dynamic (by an OFFSET-COUTNA-based named range) and still be able to add this one static number.

    oś.jpg

    Nonetheless, every time I combine the two, the dynamicity of the name-based range is lost and the chart automatically sets this range to static.

    Is there any know solution to this problem? I would be grateful for any hints.

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

    Re: A static-dynamic bar chart - is it possible?

    You can use a non contiguous reference.

    There are 3 named ranges to provide the dynamic labels and data.
    A couple of formula to calculate the averages

    You can chose the start period via a data validation drop down.
    Attached Files Attached Files
    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. How to change range from static to dynamic
    By A9kurs in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-19-2019, 05:10 PM
  2. [SOLVED] Dynamic Data for a Static Chart
    By jstawski in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-21-2018, 03:56 PM
  3. [SOLVED] Making dynamic data static
    By clarkyblade in forum Excel General
    Replies: 2
    Last Post: 08-07-2014, 08:44 AM
  4. [SOLVED] VBA - Static code to dynamic
    By gan_xl in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-06-2014, 11:51 AM
  5. [SOLVED] Changing code from Static to Dynamic
    By JamesFletcher in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-29-2013, 04:33 AM
  6. [SOLVED] Make Static code Dynamic
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-05-2012, 11:09 AM
  7. Turn static RANGE to dynamic
    By Barmoley in forum Excel General
    Replies: 7
    Last Post: 11-14-2010, 04:33 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