Gantt chart style spreadsheet

[Fudge16]

Hello,

I am trying to get the name of the task to show in the gantt chart bar.

For example for task 1 I have a green bar between the dates 22/8/17 to 27/8/17. The dates are selected in Cell D60 and F60 using conditional formatting cells V59 (22/8/17) to AA59 (27/8/17) changes to green. If i change the dates in Cell D60 and F60 to 21/8/17 and 26/8/17 corresponding to cells U59 to Z59.

Here is where I am stuck;

I would like the name of the task i.e. task 1 to appear in the cell corresponding to the start date so in this example V59. However, I want this to move when the dates are changed i.e. to U59.

I cant get this to work so any help would be appreciated.

Also, I am new to this site. Is it possible to share the spreadsheet on here?

Thanks

 

[Fudge16]

Re: Help with Gantt chart style spreadsheet

Hi Peter,

You cracked it! Thank you so much for your help with this issue. I can confirm it is working perfectly with no issues now, i am very grateful for your input.

I remember you asked me earlier in a post what would happen if the dates crossed for one of the selections. For example, In the screenshot attached, A01237 has been selected for the "Apple" job (Blue) from the 30th of july until 19th of august. However, it has also been selected for the "Asda" job (Green) from the 25th of july until the 4th of august. These dates cross and it would not be possible to physically send A01237 to both places therefore it should not be allowed.

https://www.imageupload.co.uk/image/Dmpx

Do you know a way where an error message could appear for this case?

Thanks
Alex

 

[Peter_SSs]

Re: Help with Gantt chart style spreadsheet

Probably a bit much for a forum like this but see if you think this might help. Have another sheet, say called 'Conflicts' that is set up like this. I've just manually coloured columns A:B to represent different Clients but the colour is not really needed for the process.

Excel Workbook
	A	B	C	D	E	F
1	Client	Tool	Start	End	Conflicts?	7
2	Apple	A 01237	30/07/2017	19/08/2017	Conflict
3	Apple	B1	30/07/2017	19/08/2017
4	Apple	C1	30/07/2017	19/08/2017	Conflict
5	Apple	C4	30/07/2017	19/08/2017	Conflict
6	Asda	A 01237	25/07/2017	4/08/2017	Conflict
7	Asda	C1	25/07/2017	4/08/2017	Conflict
8	Whoever	C1	23/08/2017	25/08/2017
9	Whoever	C4	23/08/2017	25/08/2017
10	Another One	C1	18/08/2017	20/08/2017	Conflict
11	Another One	C2	18/08/2017	20/08/2017
12	Another One	C3	18/08/2017	20/08/2017
13	Another One	C4	18/08/2017	20/08/2017	Conflict
14	Another One	C5	18/08/2017	20/08/2017
15
Conflicts

Have a macro that performs these steps

1. Clears all the data from this sheet except for headings [Sheets("Conflicts").UsedRange.Offset(1).Clear]
2. Copies Client, Tool List and Start/End dates from the sections across the top of the 'Planning Sheet 1' (or elsewhere if you have it elsewhere and that is easier) and Pastes under each other as shown above.
3. Enters the formula shown into column E down to the bottom of the data. Note that the last row value in the formula ($14 in my example) will need to be adjusted or just made some number larger than you will ever need.
4. Check the value in F1 and if > 0, flag that there are conflicts and this sheet should be checked to see what/where they are.

 

[Peter_SSs]

Further thought to simplify the macro a bit. I'm assuming 1,000 rows in 'Conflicts' would be enough to list all Clients/Tools, but adjust if more (or less) required.

a) Change the clearing of the 'Conflicts' sheet to only clear columns A:D below the headings:
Sheets("Conflicts").UsedRange.Resize(,4).Offset(1).Clear

b) Manually put this formula in E2 and copy down to row 1000. Then step 3 above will not be needed
=IF(B2="","",IF(SUMPRODUCT(--(B$2:B$1000=B2),(C$2:C$1000<=D2)*(D$2:D$1000>=C2))>1,"Conflict",""))

 

[Fudge16]

Hi Peter,

Can you help me with the following;

https://www.imageupload.co.uk/image/D7Ba (Data Table)

https://www.imageupload.co.uk/image/D7Bi (Resultant Table)

I have created a table (image Data table) which allows all the data to be inserted and conflicts checked. It works perfectly, thanks for this. I have decided to hide it very far away in the spreadsheet due to the fact I don't want to mess around with any of the formatting of the other cells. I also want to keep it in this spreadsheet. Therefore filtering the table is not really an option without the user having to search many cells below to find the table.

What I would require is some code that finds the first say 20 "conflict data sets" and shows the client and tool in a table like in image (Resultant table). So that the user can see immediately that there is a problem.

Do you know of any coding that would be able to do this?

Regards
Alex

 

[Peter_SSs]

Alex

I think it would go something like below. I have written it as a separate routine but you might want to either include it in the routine that creates that 'far-off' list or have that other routine just call this one at the end.

Sub List_Conflicts()
  Dim cData  As Variant, cList As Variant
  Dim cRw  As Long, cFirstRw As Long, cRws As Long, cNextRw As Long

  Const cMaxListLength As Long = 20 '<- Maximum conflicts you want to report
  
  With Columns("ABB")
    'Find first data row (row below 'Client' heading
    cFirstRw = .Find(What:="Client", LookAt:=xlWhole).Row + 1
    'Find how many rows used by finding last row and subtracting
    cRws = .Find(What:="?*", SearchDirection:=xlPrevious).Row - cFirstRw + 1
    'Read the data into an array (faster than checking worksheet cells)
    cData = .Cells(cFirstRw).Resize(cRws, 6).Value
    'Set up an array for the results. Number of conflicts or your limit (20), whichever is less
    ReDim cList(1 To IIf(.Cells(cFirstRw - 2, 6).Value > cMaxListLength, cMaxListLength, .Cells(cFirstRw - 2, 6).Value), 1 To 2)
    'Work through the data array and for each conflict transfer Client & Tool to the result array
    For cRw = 1 To cRws
      If cData(cRw, 6) = "Conflict" Then
        cNextRw = cNextRw + 1
        cList(cNextRw, 1) = cData(cRw, 1): cList(cNextRw, 2) = cData(cRw, 2)
      End If
    Next cRw
  End With
  'Clear out any existing data from the reporting area in columns C:D & fill with the new conflict list
  With Range("C17:D17").Resize(cMaxListLength)
    .ClearContents
    .Resize(UBound(cList)).Value = cList
  End With
End Sub

 

[Fudge16]

Hi Peter, this works fantastically,

However, When the conflicts are now 0 and I want to update the list to remove the current "conflicts" a run time error appears, likewise when my list of 20 is full. Is there a way that this wont appear?

Furthermore, If I want to protect the workbook and still run the code, How would I code the vba to unlock the workbook and then lock it after the update labels and conflicts have been ran?

Thanks
Alex

 

[Peter_SSs]

I haven't considered the protection issue yet, but see if this is closer.

Can you clarify whether you are talking about "Protect Sheet" or "Protect Workbook" or both?

Sub List_Conflicts()
  Dim cData  As Variant, cList As Variant
  Dim cRw  As Long, cFirstRw As Long, cRws As Long, cNextRw As Long, NumConflicts As Long

  Const cMaxListLength As Long = 20 '<- Maximum conflicts you want to report
  
  With Columns("ABB")
    'Find first data row (row below 'Client' heading
    cFirstRw = .Find(What:="Client", LookAt:=xlWhole).Row + 1
    'Check if there are any conflicts
    NumConflicts = .Cells(cFirstRw - 2, 6).Value
    'If there are conflicts, then do this section
    If NumConflicts > 0 Then
      'Find how many rows used by finding last row and subtracting
      cRws = .Find(What:="?*", SearchDirection:=xlPrevious).Row - cFirstRw + 1
      'Read the data into an array (faster than checking worksheet cells)
      cData = .Cells(cFirstRw).Resize(cRws, 6).Value
      'Set up an array for the results. Number of conflicts or your limit (20), whichever is less
      ReDim cList(1 To IIf(NumConflicts > cMaxListLength, cMaxListLength, NumConflicts), 1 To 2)
      'Work through the data array and for each conflict transfer Client & Tool to the result array
      For cRw = 1 To cRws
        If cData(cRw, 6) = "Conflict" Then
          cNextRw = cNextRw + 1
          cList(cNextRw, 1) = cData(cRw, 1): cList(cNextRw, 2) = cData(cRw, 2)
          If cNextRw = UBound(cList) Then Exit For
        End If
      Next cRw
    End If
  End With
  'Clear out any existing data from the reporting area in columns C:D & fill with the new conflict list
  With Range("C17:D17").Resize(cMaxListLength)
    .ClearContents
    If NumConflicts > 0 Then .Resize(UBound(cList)).Value = cList
  End With
End Sub

 

[Fudge16]

Hi Peter,

That worked very well, thank you again!

I would like to protect the worksheet, The macro would unprotect the worksheet, run the code, protect the worksheet and allow auto filtering and if possible the ability to collapse and open rows. I have a macro for collapsing all the rows in the worksheet but I would like to be able to open and expand individual sections just as if the worksheet was not protected.

Furthermore, How would you write a macro that copies rows 100 to 141 and then pastes them at the end of the table.

https://www.imageupload.co.uk/image/DFeb

For example, copy rows 100 to 141 and then paste in row 394. Then the next time it would paste in row 435 etc.

Thanks
Alex

 

[Fudge16]

Hi Peter,

That worked very well, thank you again!

Sorry Peter, I have realized that having the conflicts table in column ABB row 10,000 ... Is quite messy. How do I adjust the code to pull from worksheet "Conflicts" Starting in column A? I tried this but it doesn't work

With [Sheets("Conflicts").Columns("A")]
'Find first data row (row below 'Client' heading

Thanks
Alex