Gantt Chart Question

[chrisdempster]

Hi there,

I work for a small charity in Scotland and I'm trying to work out a way of keeping track of timelines across a few different regions - seemed like putting together some Gantt Charts was the way to go, but after about an hour of searching on both this website and others I'm not quite finding what I'm looking for. Given the fact it's pretty much a statistical impossibility for me to be the first person to have this issue I'm starting to wonder if the reason I can't find something is because it's not actually possible.

What I am after is the ability to have a table for each region of scotland from which regional Gantt charts are made, but that a 4th "Master" Gantt chart gathers the information from each table to create a "Scotland National" Gantt Chart, organised by date not region. So far this seems possible, but ideally I would want people to be able to add rows to the tables for each region when new things come up, and for both the regional Gantt Charts and the National Gantt Charts to recognise that there has been a change in the data and update themselves accordingly showing the new row and rearranging itself so that things remain in date order.

The issue with this that I keep on coming up against is that the chart requires you to specify the cell range so adding new rows means telling both charts to go look for the extra information, and I'm just aware that there's a good chance people will forget the extra steps and things will be missed.

If there is no way around this then fair enough I'll just have to think of another system. I thought about seeing if I could use an "IF" function to get the chart to look at the next row down in the table and if there is data add another row to the chart but everything I looked at was so beyond my level of understanding that it was akin to trying to teach non-euclidean geometry to 10 year olds.

Before I continue trying to find more on how to make this work I would greatly appreciate the benefit of your greater knowledge as to whether this is even possible. Of course support in how I would build it (maybe with finger puppets and crayons) would also be amazing.

Thank you for taking the time to read this

[Richard Buttrey]

Hi,

As you describe I don't see any insurmountable problems. Changing ranges are not a problem since you just need to create a dynamic range name and use the range name in any formulae.

For a more considered response you need to mock up example workbook(s) showing what data you want to collect and how you want to present both the regional and national chart (if different) and upload here.

Is all this to be done in a single workbook, or do you have separate workbooks for each region? Obviously all in one workbook is simpler.

There are numerous GANTT chart free template dotted around. A Google search should soon bring them up.

[chrisdempster]

Richard,

Thank you for such a quick reply - I'll get to building my Gantt chart and upload it as soon as possible (although this work is done in stolen moments across my other tasks so it may take me a wee bit)

Thanks for your advice

[chrisdempster]

Hello again everyone,

So I've thrown this together. I can't use proper data but you get the picture...

Sheet 1 Sheet 2 and Sheet 3 are the ones that people would fill out. They contain simple tables where people would type in when something needs to be done by (Column E) and how much time it would take (Column D) and there's a formula in Column C which calculates the start date for the Gantt chart - I'll probably hide column C once all the kinks are worked out.

There are separate Gantts for each of these tables so people can look at just their own stuff if they want.

Sheet 9 is where I'm stumped. I couldn't find any information on pulling together a chart that would treat all the column C's across sheets 1, 2 and 3 as though they were all the same table, so it seemed my only choice was to build a table which amalgamated all 3 tables and make a chart from that one.

The way it's done here is by me just saying "The content of this cell is the content of cell C6 from sheet 3" which is fine if people aren't fiddling with the data but I'd like it to recognise when there's another line added to one of the tables and order it's own data accordingly.

Now sorry if this doesn't make any sense, (I don't really know what I'm talking about) but I thought there would be a variation of a rule that could be put in that tells a cell in the amalgamated chart to look at the cell below the one the cell above it references, and if there is data in that cell to display that data, but if there is no data move to Table 2!B5.

I had a bit of a look around and I found a formula (which I'm not allowed to put in this post for some reason) but I don't have enough base knowledge of what this formula is saying to know how to adapt it to fit my tables.

Any help anyone could give would be utterly amazing.

[JeteMc]

If the formula that you are trying to post has a < (less than sign) followed directly by text try putting a space between such as < A1 and then posting.

[Richard Buttrey]

Hi,

Using a bar chart as you've done is one way but IMO a far easier way is to use conditional formatting and have very narrow columns for each day. You can then have these on the same sheet as your start & delivery dates

See attached where I've added a few examples for February for rows 5:10

[chrisdempster]

Thank you both for your help,

Richard I'll hopefully have time to look at your changes with my sandwich over lunch.

JeteMc here is the formula I found - On the page that people are talking about it it appears that I would just paste this into all the cells on the sheet that I would use to amalgamate the tables and it will look across the other sheets for the data, but it'll also recognise when new rows are added. I tried it and it did pull through the data from 1 table but it didn't go on to grab the data from the other sheets which makes me think that the formula is sound but in needs tweaked to fit what I'm doing. But like I say, as I don't know exactly what this formula is doing I don't know where to start adapting it to fit my needs (spaces round the < signs added as instructed)


=IF(ROW() < (COUNTA(Sheet1!A:A)+1),INDEX(Sheet1!A:A,ROW()),IF(ROW() < COUNTA(Sheet1*​:Sheet2!A:A),INDEX(Sheet2!A:A,ROW()-COUNTA(Sheet1!A:A)+1),"")

[chrisdempster]

Richard

I've managed to take a look at what you suggest and I think you're right using conditional formatting does seem a lot better. I can get proper intervals on the sheet and I've been able to use the formula you've used to ascribe a number value to each cell which allows me to get the top of the spreadsheet to go red when there's more than 3 things going on in the country at once (I'm sure there's a much easier way of doing this than counting a cell which has colour as 1 and then adding it up at the bottom of the rows but it seems to work)

I seem to have got this to do everything I want it to now except have the main sheet update itself automatically when someone adds another row on to their own sheet.

I've attached my most recent draft if anyone is interested.

Thanks for your help

Chris

[Richard Buttrey]

Hi,

In order to have the conditional formatting work however many rows you have on a sheet it would be better to have a dynamic range name that automatically adjusts to cater for additional rows that are added. So for instance create a range name called say 'Calendar' and define it thus:

Please Login or Register  to view this content.

and then use this name in the Applies To address box of the conditional format.

One tip. When you create this name for sheet1 make sure you set the scope of the name to be at sheet level not workbook. This will mean that you can simply use the Copy option when you right click the tab name to create another sheet and that new sheet will have the same name 'Calendar' name but the name will apply to the new sheet and not the original sheet1.

[chrisdempster]

Richard,

It took me a few readings of what you were saying (and a little bit of googling) but I managed to do as you suggest and it works, Sheet 1 through 3 now recognise when new rows are added and add to their own Gantt chart accordingly (if you have a second I would really appreciate a wee explanation as to how this formula does this - there's a greater chance I'll remember it if I understand it).

Now all I need is for the table in Sheet 9 to recognise when the table in sheets 1-3 get updated so that the national Gantt changes as well. I'll post up here if I find anything as I'm assuming it may be handy for others in the future

[Richard Buttrey]

Hi Chris,

Using an Offset formula is the standard way of creating a dynamic range name, i.e. a range that constantly adjusts to reflect a matrix of rows and columns. The idea is that you create an 'anchor' cell and then have the formula work out how many rows and columns you want to include, hence the use of Offset. So the general form is

OFFSET(anchor_cell,row_offset,column_offset,number_of_rows,number_of_columns)

G5 is the top left hand cell of your calendar on sheet9 hence =OFFSET($G$5. You don't need to vary this anchor so we specify 0,0 as the row and column offset from G5 so now =OFFSET($G$5,0,0.
Finally we use a standard COUNTA() function to return the number of non blank cells in a range. Normally, since the values in the anchor cell column are contiguous and don't contain any blank cells, counting the number of non blank cells will be an exact count of the number of rows in your table, plus perhaps the column header and any other non blank cells that might be above G5. In most cases it doesn't matter that the number of rows returned by the COUNTA is slightly more than the number of rows in your table since all you're doing is creating a slightly larger range than your table. So now we add in the COUNTA(Sheet9!$B:$B) which will return the value 38. There are 366 columns between column G and column NH (inclusive) on sheet 9 so finally we have

Please Login or Register  to view this content.

You could of course replace the '366' with a COUNTA(Sheet9($1:$1) which returns the value 366. This would be necessary if you added more columns to your calendar.

I'm puzzled by your request that Sheet 9 should recognise changes to the other sheets since most of the formulae (not all for some reason) in columns C&E already refer directly to the dates in those other sheets so sheet 9 should change automatically.

[chrisdempster]

Richard

Thank you for this I'm surprised to say that I feel as if I follow the logic. Sorry I didn't make myself clear, currently the cells in the table on sheet 9 just refer to the content of the other tables (or at least they should, not sure what happened with the other ones) this means that they will automatically update if the data in these cells is changed.

What I'd like to be able to do is for someone to add a row to the table in sheet 3 and for the table in Sheet 9 to recognise this and incorporate the extra row into itself automatically. Currently if someone wanted to add a row to sheet 3 in order for sheet 9 to reflect this they'd have to also manually update sheet 9

I found this formula

=IF(ROW() < (COUNTA(Sheet1!A:A)+1),INDEX(Sheet1!A:A,ROW()),IF(ROW() < COUNTA(Sheet1*​:Sheet2!A:A),INDEX(Sheet2!A:A,ROW()-COUNTA(Sheet1!A:A)+1),"")

Which from the chat around it seemed to be the sort of thing I'm after, now that I have a bit of an understanding of the function of COUNTA I might be able to get it to work.

[chrisdempster]

Hello Again All,

I've taken a crack at the code to amalgamate all 3 tables across 3 sheets into one large one on a new sheet and frustratingly it almost works (Almost being the important word in that sentence.

The Formula I've used (although full disclosure I don't totally understand it myself is

=IF(ROW() < (COUNTA(Sheet1!B:B)+1),INDEX(Sheet1!B:B,ROW()),(IF(ROW() < COUNTA(Sheet1!B:B, Sheet2!B:B),INDEX(Sheet2!B:B,ROW()-COUNTA(Sheet1!B:B)+1),IF(ROW() < COUNTA(Sheet1:Sheet3!B:B),INDEX(Sheet3!B:B,ROW()-COUNTA(Sheet1!B:B)+1)))))

This appears to work to begin with - it populates the Gantt chart alongside the table as per the conditional formatting and it seems tolerant of inserting new rows into the tables in sheets 1 through 3. I only tried it with sheet 1 to be fair but it did automatically incorporates that into the main table on sheet 9 without any issues.

but...

It doesn't show all of the rows in the tables it references. It stops short of showing all of the table in sheet 1 and overlays sheet 3 onto sheet 2 (if that makes sense) This is undoubtedly an issue born from me adapting a formula I found online with little to no knowledge of what it's actually doing, but the bit that has me puzzled is the fact that some of these rows are returning zero - which (unless I understand even less of the formula than I believe I do) shouldn't be an option. I thought the formula should either reference another cell or show "FALSE"

[Richard Buttrey]

Hi,

The fundamental problem is that you are using the Row() function to compare against the count of rows on other sheets. Nothing wrong with that of course but on the summary sheet the ROW() function when copied down is returning the row number in which the formula appears. So if it's in B5 it returns the value 5, and compares this with a count of the rows on sheet1. So for B5:B19 that's fine. However on the next row which needs to pick up the first value from sheet2, that value is on Row 5 on sheet2 but the ROW() bit of the formula now in B20 on the summary will return the value 20. But since you're using this as the INDEX reference for sheet 2 you need to deduct 15 in order that the Index will be 5 and hence you'll pick up B5 on sheet2.

The same applies for the next sheet3 but this time you have to adjust the ROW() result by the counts of both sheet1 & sheet2, i.e. 27.

Since all these COUNTA() functions make the overall formula quite large and cumbersome it therefore makes sense to use a helper cell for each sheet which contains the count and simply refer to these helper cells in the formula.

So in the attached I've added three helper cells A4:A6 which count the number of values in each sheet. A4 for instance is =COUNTA(Sheet1!B:B)-1 . The minus 1 is because there's a column label in each sheet in B4 and you don't want to include this when comparing the summary row with the sheet row. You can of course hold these formulae anywhere, and it might be a smart move to give each a name so that you can use the name in the formula.

Now in B4 on the Summary you can have a shortened formula thus:

Please Login or Register  to view this content.

I've only updated column B but it should be a simple matter to follow the same idea across into columns C:E. I think you can simply copy them across since the formula I believe does have the right mix of absolute and relative cell refs. but double check anyway.

[Richard Buttrey]

...Addendum

Hi Chris,

I'm not sure how many individual sheets you have but if more than the three in your example then using formulae for this will be unwieldy since for every new sheet you add you'll need to extend the formula.

If it were me I'd have a simple macro so that each time you wanted to see the summary the macro would first erase the B:E data on the Summary sheet and then copy the data from columns B:E on the individual sheets to the Summary sheet stacking each set of sheet data underneath the previous sheet's data on the summary sheet.

The macro could be run in response to clicking a button or automatically every time you activate the Summary sheet.