Excel variables X values

Question

I have two working days dates, first_date, second_date.

I have a data table with decreasing working date:

second date value 1

second date - 1 value 2

second date - 2 value 3

.... ....

firstdate value n

firstdate -1 value n + 1

... ...

(Data table doesn't stop at first date).

I want to plot all values between first and second date, only for working days.

I've found some ways with a macro, but I want it to be automated.

It's easy to calculate the number of line with =NETWORKDAYS(firstdate,seconddate), but I can't manage to type this function into the series X/Y values.

I have something like ='sheet1!$D$4:$D$33.

I want something like ='Sheet1'!$D$4:$D$ & NETWORKDAYS(firstdate,seconddate)

Someone know how to type that ?

I've tought about putting NETWORKDAYS(firstdate,seconddate) in a cell and use the cell.value in the formula. How does it works ?

Answer 1

You can use the OFFSET function to define a dynamic Named Range. This Name can then be used in the series formula.

In my example I have the start_date and end_date in cells D1 & E1 respectively (if not, modify the formula as necessary).

Step 1 Open the Name Manager, add a new name like "Workdays_Data" and in the Refers To: box, put the formula:

=(OFFSET(Sheet1!$D$4,0,1,NETWORKDAYS(Sheet1!$D$1,Sheet1!$E$1),1))

Then close the Name Manager dialog.

Step 2 Select the data series, and in the formula bar, replace the range that refers to the Values with the new Name, Workdays_Data. The part you need to modify is highlighted:

After you make the change, the formula bar should look like:

If you do this, then the chart should update any time you change the end date.

With some additional tweaking to the Name definition, you could modify this to also account for a dynamic start date. For example, you could replace the "0" in the Name formula with a Match function, so that the name formula reads:

=(OFFSET(Sheet8!$D$4,MATCH(Sheet8!$D$1,Sheet8!$D$4:$D$11,FALSE)-1,1,NETWORKDAYS(Sheet8!$D$1,Sheet8!$E$1),1))

You could then create another Name called Weekdays_XValues and use it for the category labels in the chart, so that those update as well:

=(OFFSET(Sheet8!$D$4,MATCH(Sheet8!$D$1,Sheet8!$D$4:$D$11,FALSE)-1,0,NETWORKDAYS(Sheet8!$D$1,Sheet8!$E$1),1))

The end result should be a fully dynamic chart based on the chosen start/end dates, like: