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

 

[Peter_SSs]

Re: Help with Gantt chart style spreadsheet

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

Welcome to the MrExcel board!
No, you cannot upload an actual file in this forum. However, you can post small screen shots using one of the methods shown in the link from my signature block below.

 

[Fudge16]

Re: Help with Gantt chart style spreadsheet

Welcome to the MrExcel board!
No, you cannot upload an actual file in this forum. However, you can post small screen shots using one of the methods shown in the link from my signature block below.

My internet is bad as im offshore. Any ideas on how to solve my problem?

 

[Peter_SSs]

Re: Help with Gantt chart style spreadsheet

Any ideas on how to solve my problem?

We are a bit short of information, but see if this helps.

1. I'm a little confused by the rows. In your example, the start and end dates are in row 60, but your coloured cells are in row 59.
2. We don't know where the task name is. I'll assume column A on the same row as the start/end dates (row 60 for your example).
3. I assume that your Conditional Formatting refers to a row of dates somewhere above row 59. We don't know which column those date headings start in, or which row they are in. I will assume they are in row 1 and start in column S.

Given all of the above, in cell S59, try this formula and copy it across all the date columns.

=IF($D60=S$1,$A60,"")


If that doesn't work and you can't adapt it to fit what you have, you will need to give enough detail (or screen shot as advised) to re-construct what you have.

 

[Fudge16]

Re: Help with Gantt chart style spreadsheet

We are a bit short of information, but see if this helps.

1. I'm a little confused by the rows. In your example, the start and end dates are in row 60, but your coloured cells are in row 59.
2. We don't know where the task name is. I'll assume column A on the same row as the start/end dates (row 60 for your example).
3. I assume that your Conditional Formatting refers to a row of dates somewhere above row 59. We don't know which column those date headings start in, or which row they are in. I will assume they are in row 1 and start in column S.

Given all of the above, in cell S59, try this formula and copy it across all the date columns.

=IF($D60=S$1,$A60,"")


If that doesn't work and you can't adapt it to fit what you have, you will need to give enough detail (or screen shot as advised) to re-construct what you have.

Screenshot of tool planner - Free Image Hosting

Thanks for your quick response, a screenshot is shown above if you follow the link.

I have tried what you suggested before however my problem is the cell sizes are small and the cells cut off the rest of the text. I know I have a color code but I really want the text to be on the colored bar also.

I have been stuck on this for a while.

If you can think of a way around this it would be much appreciated.

Thank you

 

[Peter_SSs]

Re: Help with Gantt chart style spreadsheet

.. my problem is the cell sizes are small and the cells cut off the rest of the text. I know I have a color code but I really want the text to be on the colored bar also.

If you can think of a way around this it would be much appreciated.

I think that you would need vba to do that. However, from the buttons on your screen shot it appears that you already have vba involved. So is that an option - perhaps another button to add the labels?

If so, can you clarify the following ..

1. What, exactly, is in G56, H56 etc? Are they actual dates just formatted to show the day number?
2. If the answer to 1. is that they are not actually dates, what is your Conditional Formatting formula for cell G59 (& applied across the row presumably)?
3. Can you confirm that all the Tasks listed in col C from row 60 down get highlighted only in row 59? (If so, is there ever any overlap of dates? If so, how would that colouring work?)
4. Can you confirm that D60 down and F60 down are actual dates and not text? I'm asking particularly because of the "22.8.17" format in column D.

Depending on the above answers, there may be more questions later.

I assume that you also realise that the Task Name may not actually fit completely within the coloured area. For example the text in your screen shot "758384 Aberdeen, Scotland" is physically longer than the 5 blue cells M59:R59, so it may "spill over" into further columns.

 

[Fudge16]

Re: Help with Gantt chart style spreadsheet

I think that you would need vba to do that. However, from the buttons on your screen shot it appears that you already have vba involved. So is that an option - perhaps another button to add the labels?

If so, can you clarify the following ..

1. What, exactly, is in G56, H56 etc? Are they actual dates just formatted to show the day number?
2. If the answer to 1. is that they are not actually dates, what is your Conditional Formatting formula for cell G59 (& applied across the row presumably)?
3. Can you confirm that all the Tasks listed in col C from row 60 down get highlighted only in row 59? (If so, is there ever any overlap of dates? If so, how would that colouring work?)
4. Can you confirm that D60 down and F60 down are actual dates and not text? I'm asking particularly because of the "22.8.17" format in column D.

Depending on the above answers, there may be more questions later.

I assume that you also realise that the Task Name may not actually fit completely within the coloured area. For example the text in your screen shot "758384 Aberdeen, Scotland" is physically longer than the 5 blue cells M59:R59, so it may "spill over" into further columns.

Hi Peter,

1. G56=F53 and H56=G56+1. This cell is then custom formatted to dd just to show the day.
2. The conditional formatting is =AND(G$56>=$D60,G$56<=$F60) this will give the green bar. A similar formula has been used for the blue bar.
3. Yes, this is another problem I have. In this case if the dates overlap the green color overrides the blue color. I will need to open another discussion to solve this problem at a later date.
4. Yes, they are dates (UK style). They are pulled from another cell in the spreadsheet.

Yes, for what we plan usually takes weeks so this should not be an issue and the color bar will always be longer than the text. I just used a short range date to get it in the screen shot.

If you can advise on some vba code that would do the trick using a button it would be greatly appreciated. I have only been recording macros as I am new to this.

Best regards
Alex

 

[Peter_SSs]

Re: Help with Gantt chart style spreadsheet

Test on a copy of your workbook.

Sub Add_Labels()
  Dim firstcol As Long, lastcol As Long, cols As Long
  Dim TaskCell As Range, Found As Range
  
  Const FirstDateCol As String = "G"
  Const DateRow As Long = 56
  Const ColourRow As Long = 59
  
  firstcol = Columns(FirstDateCol).Column
  lastcol = Cells(DateRow, Columns.Count).End(xlToLeft).Column
  cols = lastcol - firstcol + 1
  Cells(ColourRow, firstcol).Resize(, cols).ClearContents
  For Each TaskCell In Range("C60", Range("C" & Rows.Count).End(xlUp))
    Set Found = Rows(DateRow).Find(What:=TaskCell.Offset(, 1).Value, LookIn:=xlFormulas, LookAt:=xlWhole)
    If Not Found Is Nothing Then Intersect(Found.EntireColumn, Rows(ColourRow)).Value = TaskCell.Value
  Next TaskCell
End Sub

 

[Fudge16]

Re: Help with Gantt chart style spreadsheet

Test on a copy of your workbook.

Sub Add_Labels()
  Dim firstcol As Long, lastcol As Long, cols As Long
  Dim TaskCell As Range, Found As Range
  
  Const FirstDateCol As String = "G"
  Const DateRow As Long = 56
  Const ColourRow As Long = 59
  
  firstcol = Columns(FirstDateCol).Column
  lastcol = Cells(DateRow, Columns.Count).End(xlToLeft).Column
  cols = lastcol - firstcol + 1
  Cells(ColourRow, firstcol).Resize(, cols).ClearContents
  For Each TaskCell In Range("C60", Range("C" & Rows.Count).End(xlUp))
    Set Found = Rows(DateRow).Find(What:=TaskCell.Offset(, 1).Value, LookIn:=xlFormulas, LookAt:=xlWhole)
    If Not Found Is Nothing Then Intersect(Found.EntireColumn, Rows(ColourRow)).Value = TaskCell.Value
  Next TaskCell
End Sub

Unfortunately, this did not work the code clears the text and then no text appears and all the cells remain blank. Is it possible to share with you the workbook?

 

[Peter_SSs]

Re: Help with Gantt chart style spreadsheet

Is it possible to share with you the workbook?

As this is a public forum, we prefer to keep everything public (ref no. 4 of the Forum Rules)

If you could provide a small sample directly in this thread as I mentioned in post 2, that should do. From the screen shot data you posted earlier, just columns C:Z and rows 55-61 should do. Failing that you could upload a small sample file to a public file-share site (eg DropBox) and provide a link to it here.