+ Reply to Thread
Results 1 to 11 of 11

dynamic range for series data horizontal

  1. #1
    Forum Contributor
    Join Date
    02-06-2013
    Location
    toronto
    MS-Off Ver
    Excel 2010
    Posts
    103

    dynamic range for series data horizontal

    my data set is horizontal and new data is added by inserting a column in B every day ...so essentially data is being added to the left column rather than the most right column.


    offset formula doesn't seem to work because the reference point changes due to the insertion of a new column (eg the reference point B2 changes to C2 after inserting a new column the next day)

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: dynamic range for series data horizontal

    Can't you use A2 as the starting point, and adjust the other parameters accordingly?

    You might want to think about using INDEX instead of OFFSET, as it is not volatile.

    You haven't told us what formula you are using.

    Pete

  3. #3
    Forum Contributor
    Join Date
    02-06-2013
    Location
    toronto
    MS-Off Ver
    Excel 2010
    Posts
    103
    offset(b2,0,0,counts(b2:xfd2))

    in a2 there is a text ...not sure if that would affect 5hr chart
    Last edited by AliGW; 12-13-2019 at 10:21 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Contributor
    Join Date
    02-06-2013
    Location
    toronto
    MS-Off Ver
    Excel 2010
    Posts
    103
    not sure how to use index in this case
    Last edited by AliGW; 12-13-2019 at 10:22 AM. Reason: Please don't quote unnecessarily!

  5. #5
    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 range for series data horizontal

    Well, if you read the yellow banner it might be easier than guessing...

  6. #6
    Forum Contributor
    Join Date
    02-06-2013
    Location
    toronto
    MS-Off Ver
    Excel 2010
    Posts
    103

    Re: dynamic range for series data horizontal

    said was using the offset formula? anyway...

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: dynamic range for series data horizontal

    Try it like this, then:

    offset($A2,0,1,count($A2:AAA2))

    I presume the function is COUNT, as I don't recognise COUNTS. I'm not sure why you need to go out to column XFD - surely this will cause an error when you try to insert a new column B?

    Hope this helps.

    Pete

  8. #8
    Forum Contributor
    Join Date
    02-06-2013
    Location
    toronto
    MS-Off Ver
    Excel 2010
    Posts
    103

    Re: dynamic range for series data horizontal

    typo...it was countA, not counts...was using mobile phone at the time...will try it out
    Last edited by AliGW; 12-13-2019 at 10:22 AM. Reason: Please don't quote unnecessarily!

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: dynamic range for series data horizontal

    As you are counting from A2, that will give you one more cell than you need in your range, which presumably will be a blank cell. You can avoid it by doing this:

    offset($A2,0,1,countA($A2:AAA2)-1)

    Hope this helps.

    Pete

  10. #10
    Forum Contributor
    Join Date
    02-06-2013
    Location
    toronto
    MS-Off Ver
    Excel 2010
    Posts
    103

    Re: dynamic range for series data horizontal

    on another note, I noticed for chart series data, you cannot simply enter a named range or formula directly...the workbook name needs to appear first....I manually typed in the workbook name manually, but would there be a quicker way to enter it without manually typing it in ? it's also annoying because the workbook name in a formula requires apostrophes and the exclamation mark.

  11. #11
    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 range for series data horizontal

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)

+ 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 named range using OFFSET for chart series data isn't quite right....
    By mnschwarz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-27-2019, 03:59 AM
  2. [SOLVED] Horizontal dynamic range not working
    By steam73 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-01-2017, 09:32 AM
  3. [SOLVED] Finding the (Dynamic) End of a Horizontal Range
    By Lemmy Kickit in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-12-2016, 06:52 PM
  4. Data Series Formatting with a Dynamic Range
    By Shipoopi in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-17-2015, 04:28 AM
  5. [SOLVED] Horizontal left and right bar charts based on different data series
    By meiselsan in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-18-2015, 09:12 AM
  6. [SOLVED] Add totals to each data series in a horizontal stacked bar chart.
    By maacmaac in forum Excel General
    Replies: 2
    Last Post: 01-13-2015, 12:04 PM
  7. Dynamic Chart Range for Horizontal values
    By phill_howz in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-03-2014, 11:47 AM

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