• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic chart according to cell value start & end date

Hi chandoo,

I have a dashboard tool, where data gets entered via userforms, and gets transferred to datatables. The first column of the datatables are the dates, with every date given a single row.

My dashboard shows specific data from this table in a chart. I have 2 dropdown lists in a cell (via validation) in which a user can select a start & end date. I want my charts to be dynamic according to these cell values.

I guess this can either be done with sorting the datatable according to these dates, or adjusting the chart's x-axes (the date) according to these dates.

What should I do, and how can I do this?

Thanks alot!
 
Hi Hugo,

Please note that in general , if you upload either your working file , or at least a sample file , you are likely to get a solution faster ; this is even more so , where you are looking for code ; even if someone would like to help you out with the code , it is less likely that they will generate the test data , needed to test the code thoroughly , on their own. If you can upload your file , you save them the trouble , and in the process are helping yourself to get a solution faster.
 
Hi Hugo ,

I did not understand your comment about sorting the data table according to the two selected dates. The data should already be sorted on the dates , so that the data is in ascending order of dates.

As I understand your requirement , the start date is selected in one cell , and the end date in another cell. The validation should ensure that the end date is after the start date.

I assume that the two selected dates will be present in the dates column.

If this is so , then you can have a named range for the X-axis , which will have the formula :

=OFFSET(dates , MATCH(start date , dates , 0)-1 ,, MATCH(end date , dates , 0) - MATCH(start date , dates , 0))

Of course , you need to ensure that your chart series are also similarly derived.

Narayan
 
Sorry, with sorting the dates according to the 2 dates in the cells, I meant that the table could be filtered thus showing only dates that are between start & end date.

Thanks for the tips, I will see if I can get this to work! Will get back here.

What do you mean with 'make sure that your chart series are also similarly derived'?

Does my other chart data (the other series) also need to have the same named range, with the offset, or does this automatically adjust after the x-axis has been updated?
 
Hi Hugo ,

Adjusting the X-axis should automatically select the correct range for the chart series too , but I have not verified this ; try it out and see if it works ; if it does not , then you will have to define the chart series in the same manner , so that whenever the start date and end date are changed , the series also change accordingly.

Narayan
 
Allright, thanks for your replies.

I have now tried to do this. When I enter the following named range in the data for the chart, I get an error.

I try to enter this reference formula as the data source for the horizontal axis category labels:

=OFFSET(MasterTable[[#Headers],[Date]],MATCH(DASHBOARD!H39,MasterTable[Date],0)-1,,MATCH(DASHBOARD!$H$41,MasterTable[Date],0)-MATCH(DASHBOARD!$H$39,MasterTable[Date],0))

This is the same as the formula you just gave me, but then with structured references. I tried to see if this worked without the structural references, and it didn't.

Am I doing something wrong? I just click 'select data', and enter this function to the x-axis. It says the function is not valid.
 
Hi Hugo ,

Can you try this ?

=OFFSET(MasterTable,[Date],MATCH(DASHBOARD!H39,MasterTable[Date],0)-1,,MATCH(DASHBOARD!$H$41,MasterTable[Date],0)-MATCH(DASHBOARD!$H$39,MasterTable[Date],0))

changing the initial :

MasterTable[[#Headers],[Date]]

to

MasterTable[Date]

Narayan
 
Unfortunately, I still get the same error message.

The table contains +/- 800 dates, does it maybe have something to do with the amount?
 
Hi Hugo ,

There is only one possibility ; that your table is not sorted.

Can you separate out the components of the above formula , and enter them in worksheet cells and see what they display ?

1. =MATCH(DASHBOARD!H39,MasterTable[Date],0)-1

2. =MATCH(DASHBOARD!$H$41,MasterTable[Date],0)

3. =MATCH(DASHBOARD!$H$41,MasterTable[Date],0)-MATCH(DASHBOARD!$H$39,MasterTable[Date],0)

If your table is sorted , then 2 should be bigger than 1 , otherwise it will generate an error.

Narayan
 
Sorry, it is fully sorted, as the dates depend on a start date, which is the first row of the table. All the other date entries in the table, are just formula based like this: = (above cell)+1.

Results for the match funtions are 0, 793, 792.

I want to make sure I'm doing the right thing.

My chart are currently set up just so that they show the entire date range on the x-axis. This is way to big. It currently looks like this: ='BACKPAGE CALCULATION 1'!$B$14:$B$825 which is the same as =MasterTable[Date]. It automatically switches to a cell reference instead of a structural reference.

It just keeps giving me the same error when I try to enter the offset reference in the data sources.
 
It's all in the date format. The input boxes for the start & end date are formatted as "mmm-yy" though, but the value that's actually in the cell still shows the standard date format (dd-mm-yyyy).

By the way, every date in the workbook is dynamic, based on one single date inputted at the start of the project. That means that the validation lists in which a user must pick a date are dynamic and the dates in the datatables are dynamic, all based on the same starting date.
 
Hi Hugo ,

Let me explain.

The file I uploaded is charting the data in column D ( Minutes ) vs. the data in column A ( Date ).

The chart series is defined as : =Sheet1!$D$2:$D$4147 which is the entire column of data.

The X-axis is defined as a named range X_axis , referring to :

=OFFSET(customer_time[Date],MATCH(StartDate,customer_time[Date],0)-1,,MATCH(EndDate,customer_time[Date],0)-MATCH(StartDate,customer_time[Date],0))

The StartDate and EndDate named ranges are as follows :

StartDate : =Sheet1!$I$1

EndDate : =Sheet1!$I$2

If you change the values in either of the above 2 named ranges , the X_axis named range automatically changes , and as a result , the chart also changes.

Is this not what your original question was about ?

The only point to note is that the dates in column A are not just dates but a combination of a date and time ; when you change the values in StartDate or EndDate you will have to enter the exact values which are present in the table.

Narayan
 
Hi guys, thanks for your replies! I finally figured it out, and as I kinda expected, it was just a small minor thing.

So as I said before, I tried to just enter my dynamic range in the data source as this:

=OFFSET(MasterTable[[#Headers],[Date]],MATCH(DASHBOARD!H39,MasterTable[Date],0)-1,,MATCH(DASHBOARD!$H$41,MasterTable[Date],0)-MATCH(DASHBOARD!$H$39,MasterTable[Date],0))

This gave me the error : That function is not valid.

In the meantime, I tried to just use this function to define a name first, to check if it would work. I made a dynamic named range, called DateRange3, with this formula, and excel recognized the precise range I wanted it too. When entering this named range as the data source, it still gave me the same error though... I entered it as =DateRange3, as I normally would when using named ranges in formulae.

The little thing that I did not do is however, to add the file name in the syntax. After spitting through google I found a topic with someone who had the exact same error when trying to enter a dynamic named range.

I should have just added the filename in front of the named range in the syntax... Like this =filename.xlsm!DateRange3

And now it works.. That's it. I'm learning new things each day! I hope this was not something obvious to you guys, and that you're not starting to pull your hear while reading this quite simple solution. Anyway, I'm happy to have it working now, and want to thank you for all your help!

Cheers!
 
Back
Top