+ Reply to Thread
Results 1 to 12 of 12

Dynamic Range/Dynamic Chart Using Cell Value for Reference and without CountA

  1. #1
    Registered User
    Join Date
    07-16-2014
    Location
    Seattle, WA
    MS-Off Ver
    2013
    Posts
    60

    Angry Dynamic Range/Dynamic Chart Using Cell Value for Reference and without CountA

    I want to create a dynamic chart that populates values for 3 different categories over a 12 month period based on the current month. I have read up about using the offset function so I have started by naming ranges using the INDIRECT function to use the cell that contains the row indicator for the current month to indicate the first value in the offset argument. Then I want to look 11 rows above (-11), in the same column (0), with a height of 12 (12), and a width of 1 (1). In my head this produces a 1*12 range that includes the data I want to chart for this category.

    Please Login or Register  to view this content.
    These data are on Sheet2 and I want to create the graph on Sheet1. However, when I go to create a chart on Sheet1 and input the Chart Range as CumRange, I receive an error that says

    Please Login or Register  to view this content.
    And this is where I'm stuck. Any help would be much appreciated!
    Attached Files Attached Files
    Last edited by kelseygueldalewis; 09-11-2017 at 03:33 PM.

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Dynamic Range/Dynamic Chart Using Cell Value for Reference and without CountA

    you don't need to use indirect
    just add sheetname for named (corrected) Dynamic Range
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-16-2014
    Location
    Seattle, WA
    MS-Off Ver
    2013
    Posts
    60

    Re: Dynamic Range/Dynamic Chart Using Cell Value for Reference and without CountA

    Thanks Tim. I am trying to replicate these corrections into my unedited sheet I get a circular references error.
    In your comment you say just add the sheetname for named Dynamic Range, however in the example you use the workbook name, not the sheetname - does it make a difference?
    Any ideas on the circular error?

    I cleared and hid the cells from my original document to show you how it looks more realistically on my end. Can you get it to work on teh Dashboard and Raw Data Sheets?
    Attached Files Attached Files
    Last edited by kelseygueldalewis; 09-07-2017 at 01:26 PM. Reason: Attachment

  4. #4
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Dynamic Range/Dynamic Chart Using Cell Value for Reference and without CountA

    you should add sheetname to a named dynamic range, used as data source for a graph
    Attached Images Attached Images

  5. #5
    Registered User
    Join Date
    07-16-2014
    Location
    Seattle, WA
    MS-Off Ver
    2013
    Posts
    60

    Re: Dynamic Range/Dynamic Chart Using Cell Value for Reference and without CountA

    I am using the Sheet name, in this case the sheet name is 'Raw Data' but I still get teh same error. That there is a problem with one or more formula references in the worksheet.

    I ran and error check on the dashboard and raw data sheets but there were no errors.
    Attached Images Attached Images
    Last edited by kelseygueldalewis; 09-07-2017 at 03:50 PM.

  6. #6
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,124

    Re: Dynamic Range/Dynamic Chart Using Cell Value for Reference and without CountA

    dataTABLE =chartdata!$B$4:INDEX(chartdata!$D:$D,COUNTA(chartdata!$D:$D)+1,1)
    Column_B =INDEX(dataTABLE,chartdata!$G$3-11,1):INDEX(dataTABLE,chartdata!$G$3,1)
    Column_C =INDEX(dataTABLE,chartdata!$G$3-11,2):INDEX(dataTABLE,chartdata!$G$3,2)
    Column_D =INDEX(dataTABLE,chartdata!$G$3-11,3):INDEX(dataTABLE,chartdata!$G$3,3)

    I changed the name of that sheet ( )
    Attached Files Attached Files
    Last edited by protonLeah; 09-07-2017 at 07:07 PM.
    Ben Van Johnson

  7. #7
    Registered User
    Join Date
    07-16-2014
    Location
    Seattle, WA
    MS-Off Ver
    2013
    Posts
    60

    Re: Dynamic Range/Dynamic Chart Using Cell Value for Reference and without CountA

    Hi Ben,

    When I tried that method, as I was attempting to input the data for the chart I got a warning that said the function wasn't valid.

    Any other ideas?

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,124

    Re: Dynamic Range/Dynamic Chart Using Cell Value for Reference and without CountA

    I need more info. That chart was made with Excel 2007 so I don't know what "function" you are referring to.

  9. #9
    Registered User
    Join Date
    07-16-2014
    Location
    Seattle, WA
    MS-Off Ver
    2013
    Posts
    60

    Re: Dynamic Range/Dynamic Chart Using Cell Value for Reference and without CountA

    okay - I was able to update the graph that tim uploaded by adding my information - the last thing I am stuck on is the horizontal axis. I can get it to update by naming a title range - but the last month should be the reference month, but I can only get it so that the last month is the month after the reference month because the named range [=OFFSET(l!$A$1,l!$G$3-11,0,12,1)] can't go back farther than A1.
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,931

    Re: Dynamic Range/Dynamic Chart Using Cell Value for Reference and without CountA

    If I understand correctly changing the formula for 'Axis Title' so that it reads as follows should do what you want:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  11. #11
    Registered User
    Join Date
    07-16-2014
    Location
    Seattle, WA
    MS-Off Ver
    2013
    Posts
    60

    Re: Dynamic Range/Dynamic Chart Using Cell Value for Reference and without CountA

    Yes that solved it! Thanks!

  12. #12
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,931

    Re: Dynamic Range/Dynamic Chart Using Cell Value for Reference and without CountA

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. [SOLVED] dynamic range, counta and offset combination
    By chomo in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-13-2016, 02:53 AM
  2. OFFSET, COUNTA or SHOULD BE COUNTIF Function for dynamic range for Chart
    By ahteddy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-24-2015, 10:50 AM
  3. Replies: 1
    Last Post: 04-10-2015, 11:53 AM
  4. Why dynamic range doesnt work with COUNTA
    By Kushal_1991 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-31-2013, 11:17 AM
  5. Dynamic Range Column Chart w/ Dynamic Avg Line (Excel 2007)
    By BrokenBiker in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-04-2012, 11:40 AM
  6. CountA - Dynamic Range - Macro
    By elcentro3m in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-10-2007, 02:54 PM

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