+ 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
    162

    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
    2013 / 2016 / 365
    Posts
    7,042

    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)

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