Can a Start date and End date link from different cells to be on one line in Gantt

Nicolette Marinos

Hello!

I'd like to create a master gantt chart where the beginning date is linked to one action in another sheet, and then end date is linked to another action in the same other sheet.

For example, if I said Project A had an inspection and the inspection request happened on 3/31, but the inspection didn't happen until 6/31, the gantt bar would show those two dates as a beginning and end on one line.

I realize this could be done with just having the two dates be the beginning and end of that section on the linked sheet, but likely they will be in different sections on the sheet.

Any guidance or workarounds?

Genevieve P.

Hi @Nicolette Marinos

The auto-fill for the End Date comes into play if you have Dependencies enabled in your Project Settings.

You can use a Gantt chart without Dependencies! Right click on any column name, select "Project Settings", and make sure this box is un-checked:

You'll also want to make sure the Start Date column and the End Date column are Date columns and are the ones selected in this Project Settings window. (See: Create and Work With a Gantt Chart.) Let me know if this helps!

Cheers,

Genevieve

Genevieve P.

Hi @Nicolette Marinos

I presume you mean that these actions/sections are different rows, is that correct? Is the date for both the "start" and "end" of your Gantt line housed in the same Date column or are they two different columns (as well as two different rows)?

We could create a helper sheet that uses formulas to pull in the two dates into the same row (which could then create a Gantt chart), however there would need to be a unique identifier to search for across the two sheets.

For example, if this is your source sheet:

Then you can use MIN and MAX to find the oldest and newest dates, matching the "Task", to populate your helper Gantt chart, like so:

Start Date formula:

=MIN(COLLECT({Date Column}, {Task Column}, [Primary Column]@row))

End Date formula:

=MAX(COLLECT({Date Column}, {Task Column}, [Primary Column]@row))

Will this work for you? If not, it would be helpful to see a screen capture of your source sheet (please block any sensitive data), indicating where the "start" and "end" dates are, and I'm happy to help further.

Cheers!

Genevieve

Nicolette Marinos

Hi Genevieve! I'm fairly new to smartsheet, but I tried that formula and I'm not sure if I just didn't do it right or it won't work for my needs. For your equation to work would do the begin task and the end task have to be the same name?

I made a dummy sheet in a similar style to the original sheet

Ideally I would want the gantt to show how long it takes from the submit for review to the client review.

Thank you!

Genevieve P.

Hi @Nicolette Marinos

Thank you for clarifying! Based on this information there are two potential ways you could create this Gantt chart.

Cell Linking

If you want to set this up in a different sheet, the easiest thing to do may be to cell-link those specific date cells into your second, Gantt sheet like so:

Then select the dates individually:

Helper Column & Report

As an alternative, you could create a "helper column" in the source sheet to grab the date when Submitted. In my sheet I used a formula to grab the date and put it in the "Submitted" column:

=[Start Date]$3

Then you could create a Report and use different columns in your Report's Gantt chart:

Would this be better for you? Let me know if you need help creating the Report or if you have any other questions!

Cheers,

Genevieve

Nicolette Marinos

Hi Genevieve

When I link the start date, the end date automatically fills in for that same row. Is there any way to have the end date be linked to another row, and have it show up as one line on the gantt? I've tried to link start and ends dates from different rows in just text columns, but the gantt bar doesn't show up.

Genevieve P.

Hi @Nicolette Marinos

The auto-fill for the End Date comes into play if you have Dependencies enabled in your Project Settings.

You can use a Gantt chart without Dependencies! Right click on any column name, select "Project Settings", and make sure this box is un-checked:

You'll also want to make sure the Start Date column and the End Date column are Date columns and are the ones selected in this Project Settings window. (See: Create and Work With a Gantt Chart.) Let me know if this helps!

Cheers,

Genevieve

Nicolette Marinos

AMAZING!!! thank you so much!

Genevieve P.

No problem! I'm glad that helped. 🙂