+ Reply to Thread
Results 1 to 4 of 4

Using ADDRESS Function to set the range of data for a basic chart

  1. #1
    Registered User
    Join Date
    02-11-2014
    Location
    Darwin, Australia
    MS-Off Ver
    Office 365
    Posts
    34

    Question Using ADDRESS Function to set the range of data for a basic chart

    Hi All,
    I am trying to automate a chart in a basic spreadsheet and what I normally do is set the data range from say A2:A980 but I only have data in about the first 250 cells in that column.
    I can locate the last cell with data by using the Address function [i.e. =ADDRESS(MATCH(E2,B:B,0),COLUMN(B2)) - Note E2 is the last figure value in the row B]. Col A is the Date, Col B is the values which are sporadic.

    In the attached example I use the formula to get the values into the chart '=Sheet1!$A$1:$B$260'

    What I would like to do is substitute $B$260 with an address function so If i put data in after B260 it will be picked up so the formulae may look something like this???
    '=Sheet1!$A$1:ADDRESS(MATCH(B1,A:A,0),COLUMN(A2))'

    Appreciate any advice
    Cheers Tony
    Attached Files Attached Files
    Last edited by tonez90; 01-12-2023 at 07:14 PM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Using ADDRESS Function to set the range of data for a basic chart

    I don't think you can use formulas inside a chart definition.

    However, the words dynamic chart and named dynamic range go hand in hand.

    First we want to find the last row in Column B This is given by the formula in cell H2 =MATCH(1,B:B,-1) - this means that starting at the bottom of column B find the first cell that contains a value of 1 or larger. This finds row 260.

    Then we need to make the dynamic names.

    Go to Formulas > Name Manager and type in the name you want to call the range: the first range, I called Plot_Values and its definition (refers to) is =OFFSET(Sheet1!$B$1,1,0,Sheet1!$H$2-1,1)

    What the offset command tells us is:
    -- Start in Cell B1
    -- Go down one row (so we are now in B2)
    -- Go right zero columns (so we are still in B2)
    -- From this point give me a range 260 (cell H2) - 1 rows deep and 1 column wide.

    This works out to B2:B260

    The other named range is Plot_Date =OFFSET(Plot_Value,0,-1)

    Which simply says give me a range the same as Plot_Value and shift it down 0 rows and right -1 columns.

    Then go into the chart and select Select Data and edit the value series

    Series X Values=Sheet1!Plot_Date
    Series Y Values=Sheet1!Plot_Value

    if you look at them after setting them, you will see the workbook name instead of Sheet1 because these values were defined with a scope of the entire workbook.

    Now when you add or delete values in column B, the dynamic ranges will change and so will the charts.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    02-11-2014
    Location
    Darwin, Australia
    MS-Off Ver
    Office 365
    Posts
    34

    Re: Using ADDRESS Function to set the range of data for a basic chart

    Thanks for such a quick response. This works well for my purpose.
    If anyone else has ideas I would be interested in earing them. I am learning every day.
    Cheers Tonez

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Using ADDRESS Function to set the range of data for a basic chart

    I am standing on the shoulders of a giant.

    Here is my go-to source for any charting questions: https://peltiertech.com/Excel/Charts/Dynamics.html.

+ 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. basic conversion of cell address to a range
    By marlon1_1 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-19-2020, 09:50 AM
  2. How to get a chart to use a data address that is in a cell... I.E. (indirect function)
    By exclusiveicon in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-30-2014, 09:34 AM
  3. Replies: 2
    Last Post: 05-21-2014, 09:32 AM
  4. Getting chart range from range address contained in a cell
    By Journeyman3000 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-03-2013, 08:10 PM
  5. Replies: 1
    Last Post: 10-08-2008, 11:26 AM
  6. [SOLVED] How to return the address of the range of plotted data on an XY scatter chart?
    By Ken Johnson in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 07-04-2006, 07:45 AM
  7. Passing a range of data to a function - Excel - visual basic
    By dochoa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-02-2006, 04:15 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