+ Reply to Thread
Results 1 to 2 of 2

Dynamic named range using OFFSET for chart series data isn't quite right....

  1. #1
    Registered User
    Join Date
    01-26-2019
    Location
    Panama
    MS-Off Ver
    2016
    Posts
    1

    Dynamic named range using OFFSET for chart series data isn't quite right....

    Hi guys,

    I'm using a named range using offset and counta formulae to create a dynamic chart. It works basically how I want it to however all my ranges miss the very last entry and I can't see what I've missed. This is one of my named ranges:

    =OFFSET('Branch 1'!$Q$17,0,0,COUNTA('Branch 1'!$Q$17:$Q$137))

    I'm then using the named ranges for the x and y series data in the chart to update automatically as new data is entered. Which it does, except for the final entry. It's always one short. Any idea what I've missed? Thanks.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Dynamic named range using OFFSET for chart series data isn't quite right....

    Depending or your XL version ( not mentioned in your description) you can use a table as source for your charts instead of formulas
    Select the range - Insert tab - Table and follow the wizard. Now each time you enter a value the range adapts as does the chart

    Really want a formula ?
    Try =index($q$17:INDEX(Q:Q,MATCH(9.99999999999999E307,Q:Q)) as range for numbers
    or
    =index($q$17:INDEX(Q:Q,MATCH(REPT("Z",255),Q:Q)) for text

+ 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: 0
    Last Post: 03-08-2016, 02:25 PM
  2. [SOLVED] offset in Named Range that consider the dynamic data
    By laansesu in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-29-2016, 02:29 AM
  3. add Data label using vba in a chart series with named range
    By tomjohns24 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-27-2015, 05:28 PM
  4. Excel 2010 dynamic filename for named range in chart series
    By byo18 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-24-2014, 10:30 AM
  5. [SOLVED] Dynamic Named Range As Chart Series
    By goss in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-19-2013, 05:22 PM
  6. [SOLVED] Dynamic Named Ranges & Chart Series
    By stockgoblin42 in forum Excel Charting & Pivots
    Replies: 17
    Last Post: 05-15-2013, 01:31 AM
  7. Using INDIRECT to put a Dynamic Named Range in a chart Data Source
    By mgaworecki in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-01-2012, 07:35 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