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?
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?
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks