+ Reply to Thread
Results 1 to 2 of 2

3 date line's one time chart line?

  1. #1
    Forum Contributor
    Join Date
    01-29-2019
    Location
    Drenthe, Netherlands
    MS-Off Ver
    Dutch Office 2019
    Posts
    291

    3 date line's one time chart line?

    I have 3 columns with data that is updating every day. I like the 3 columns in one line chart.

    But with the correct data with the correct date.

    I it even possible without combining te dates?
    Attached Files Attached Files

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

    Re: 3 date line's one time chart line?

    I created named dynamic ranges and used them as source data for the charts.

    Here are the two wikis that explain the bulk of what I did.
    http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges
    http://www.utteraccess.com/wiki/Dynamic_Charting

    Here is a list of the named ranges:
    Datum_Antis =OFFSET('Chem. Verbruik'!$J$6,0,0,COUNTA('Chem. Verbruik'!$J:$J)-2,1)
    Datum_Loog =OFFSET('Chem. Verbruik'!$B$6,0,0,COUNTA('Chem. Verbruik'!$B:$B)-3,1)
    Datum_Zeep =OFFSET('Chem. Verbruik'!$F$6,0,0,COUNTA('Chem. Verbruik'!$F:$F)-2,1)
    KG_IBC_Antis =OFFSET(Datum_Antis,0,2)
    KG_IBC_Loog =OFFSET(Datum_Loog,0,2)
    KG_IBC_Zeep =OFFSET(Datum_Zeep,0,2)
    Plot_Antis =OFFSET(Plot_Datum,0,4)
    Plot_Datum =OFFSET(Lookup!$D$2,0,0,COUNTIF(Lookup!$E:$E,TRUE),1)
    Plot_Loog =OFFSET(Plot_Datum,0,2)
    Plot_Zeep =OFFSET(Plot_Datum,0,3)

    In general, the offset command has five parameters and it points to a rectangular range of cells.
    - A starting cell
    - Number of rows to go down
    - Number of columns to go right
    - Number of rows to return
    - Number of columns to return.

    If the first parameter is a range instead of an existing cell, then the last two parameters are not needed. Excel will assume that the number of rows and columns to point to are the same as the original range.

    The Datum_ ranges point to the range of dates in the Datum columns. In need to identify these dates to find the maximum and minimum dates, and I will need them for future lookups.

    The KG_IBC_ ranges point to the values associated with the dates. Note that each of these is the same range as its associated Datum, except that it is shifted over by two columns.

    The Plot_ ranges are used to plot the charts.

    This brings us to the lookup sheet.

    Cells B1 and B2 are the maximum and minimum dates found in the three date ranges.

    Column D is the "hard coded" part of the spreadsheet, It starts with the minimum date and I extended the dates to the end of 2020. You can extend them even more if you wish. I could have made this more flexible with VB code, but decided to avoid VB since we do not need it for anything else.

    Column E is true if the date is less than or equal to the maximum date. I use this in the offset definition for Plot_Datum

    Plot_Loog, Plot_Zeep and PlotAntis are offset from Plot_Datum.

    The Plot_ ranges are used in the chart on the Lookup Sheet. You can Cut / Paste this chart back to the original sheet if you wish.

    As you enter data into the three tables on the original sheet, the dynamic ranges should keep up with it and the chart should change automatically.
    Attached Files Attached Files
    Last edited by dflak; 02-06-2019 at 01:44 PM.
    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.

+ 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: 01-29-2014, 08:10 PM
  2. [SOLVED] Chart Lengend to show marker style on hidden line - Line chart
    By etaf in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 08-22-2013, 05:19 PM
  3. time and date on line chart
    By swapa in forum Excel Charting & Pivots
    Replies: 14
    Last Post: 08-01-2013, 08:31 AM
  4. Replies: 1
    Last Post: 12-31-2012, 05:31 AM
  5. date and time line chart
    By ilfan83 in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 06-23-2012, 12:04 AM
  6. How to create a vertical reference line on a time series line chart
    By Bladebgii in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-23-2012, 10:17 AM
  7. Line chart - date line association gone mad!
    By Johannes Czernin in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 01-19-2005, 05:06 PM

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