Dynamic diagramm

[joshua1990]

Hey guys!

I need some help at developing a dynamic diagram (1st time).
There is a column with the date (daily), status for the workday (1 = workday) and the respective 3 key indicators for the diagram.
The main problem is that I need only the last 20 workdays, with the current date as the start line.
How can a manage this?

I think it's a combination of a when and today function but I cant handle this.

Do you have some help or any advice?

Here is a sample map.


Best regards!


DiagramDraft.xlsxDiagramDraft.xlsx

[mdmackillop]

I've added a dynamic range name and a macro to update the chart.

[Aussiebear]

I have a "method or data member not found" error. Is this something not found in Excel 2011?

[mdmackillop]

Hi Ted
Are you referring to my post?

[Bob Phillips]

I have a "method or data member not found" error. Is this something not found in Excel 2011?

Excel 2011? Mac? Lot's not found in Mac Excel VBA.

[joshua1990]

Hey mdmackillop!
Thanks for your approach (:
It's working but it's not exactly what I'm looking for (:

The diagram should present the last 20 workdays. A workday is declared with the status 1 in the Colum B.


Is this possible without VBA?
I was thinking about something like that:

Last Row: If Today (Column A) = 1 (Column B), Then show
Row 19= If Today (Column A)-1 = 1 (Column B), Then show
Row 18= If Today (Column A)-2 = 1 (Column B), Then show

[mdmackillop]

Best I can do without VBA. There is a hidden column (F) and data in cells behind the chart. Data used is highlighted with CF

[joshua1990]

I'm sorry mdm,

Its the same result like the one before.
There are also no workdays (status 0) in the diagram. It should present only real workdays (status 1).
Furthermore, it should present the last 20 workdays, not the next 20 workdays.

Maybe an extra table with the respective data (last 20 workdays) will solve the problem.

Have anyone an idea?

[mdmackillop]

Use Workday formula to list only workdays

[joshua1990]

Use Workday formula to list only workdays

Many many thanks!

It's working super (:
But I need the second column with the workday(status) as I have shown in the sample file.
There are some workdays at the weekend for special days terms in the year.
Is this possible to include it in your approach?


With best regards

[mdmackillop]

No need for column B. Just insert a row at the appropriate position and enter the date manually. The succeeding formulae should adjust automatically.

[joshua1990]

Okay cool, but I need for further research also the Not-Workdays.
The column with the status is necessary.

Please excuse me for saying this vaguely

[mdmackillop]

Similar to the earlier version. Autofilter is used to conceal non-workdays.

[joshua1990]

Similar to the earlier version. Autofilter is used to conceal non-workdays.

Thanks for your new approach, mdmackillop!

But it's not working with this structure. I need a clear structure, a clear original date set, as it is presented/ structured.
Of course, there could be new columns.

Maybe it's possible to create a table with the last 20 workdays.

The last row presents the current date
The last row -1 presents the last date -1 if it's a workday.
And so on.
Maybe a combination of Vlookup, When, today and workday-function.

Does someone have any idea?

[offthelip]

herre is a copy of a routine that I wrote to work out the last 260 working days including taking account of Bank holidays.
The holiday dates are listed in column A of sheet "Lsehols"
I thought this might be close to what you want:

Sub Datelist()Sheets("Lsehols").Select
Bankhols = Range(Cells(1, 1), Cells(77, 1))


Sheets("Dates").Select
inarr = Range(Cells(1, 1), Cells(274, 1))




indi = 0


today = Date
 If Weekday(today, vbMonday) > 5 Then
   today = today - 1
 End If
 If Weekday(today, vbMonday) > 5 Then
   today = today - 1
 End If
For i = 274 To 14 Step -1
 Dayval = False
 Do While Dayval = False
 Cday = today - indi
 
 If Weekday(Cday, vbMonday) > 5 Then
     indi = indi + 1
 Else
    Bankhol = False
    For jj = 1 To 77
     If Cday = Bankhols(jj, 1) Then
       indi = indi + 1
       Bankhol = True
       Exit For
     End If
    Next jj
        Dayval = Not (Bankhol)
 End If
 Loop
 inarr(i, 1) = today - indi
 indi = indi + 1
Next i


Sheets("Dates").Select
Range(Cells(1, 1), Cells(274, 1)) = inarr


End Sub