+ Reply to Thread
Results 1 to 3 of 3

dynamic chart values formula with dynamic starting point

  1. #1
    Forum Contributor
    Join Date
    09-05-2011
    Location
    Essex, England
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    383

    dynamic chart values formula with dynamic starting point

    Hi All

    I'm trying to create a dynamic chart range using the formula below but i'm getting an error.

    Essentially row 3 may contain dates (unless there's no data in row 6 in which case row 3 returns 0)


    what this formula should be doing is creating the offset reference by looking for the first cell that is greater than 0 taking the column number and using the row number 6 create a R1C1 style reference that indirect will evalue into a cell, then i'm looking through row 3 and counting the number of cells whose value is greater than 0 and adding 1 (as the table starts in column 2) to create the width element of the offset.

    however: I keep getting a VALUE error and can't work out why - any idea?

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,072

    Re: dynamic chart values formula with dynamic starting point

    If you set up an OFFSET Formula that returns a range, within a single cell, you'll get a #Value returned. There's nothing wrong with the formula. That doesn't mean it's giving you what you want as far as your dynamic range. Put a SUM in front of it and see if it matches up with what you would expect the sum to be.

    If you upload an example spreadsheet, I might come up with alternate methods for you.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Contributor
    Join Date
    09-05-2011
    Location
    Essex, England
    MS-Off Ver
    Excel 2003 Excel 2007
    Posts
    383

    Re: dynamic chart values formula with dynamic starting point

    you're spot on the formula I was using was working perfectly. I have c.50 dynamic named ranges and one of the first ones I wrote actually had a function misspelt which was triggering the error rather than this one. (I think i might of had calculations turned off initially)

    thank you so much for your help!

+ 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 Macro Starting Point
    By scarlettw123 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-30-2015, 07:09 AM
  2. Dynamic starting point
    By scarlettw123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-30-2015, 06:22 AM
  3. VBA - Updating Dynamic Line Chart & Inserting Dynamic Formula
    By bruno08102013 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-09-2014, 10:10 AM
  4. Dynamic chart that displays a range starting today
    By jimfrog in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-23-2006, 02:40 PM
  5. Indexing Values to Same Starting Point for Relative Return Chart
    By Notclevr in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-18-2006, 06:35 PM
  6. [SOLVED] How to add a fixed point to a dynamic chart
    By signell@msu.edu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-11-2005, 09:20 AM
  7. [SOLVED] Define a dynamic range from a specific starting point in a list
    By gwh_lbh@swbell.net in forum Excel General
    Replies: 3
    Last Post: 08-19-2005, 09:05 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