Gantt Chart Question

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.

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.

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

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.

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:

Formula:

=OFFSET(Sheet1!$G$5,0,0,COUNTA(Sheet1!$B:$B),372)

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.

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

Formula:

=OFFSET($G$5,0,0,COUNTA(Sheet9!$B:$B),366)

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.

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:

Formula:

=IF(ROW()-4<=$A$4,INDEX(Sheet1!B:B,ROW()),IF(ROW()<=$A$4+$A$5+4,INDEX(Sheet2!B:B,ROW()-$A$4),IF(ROW()<=$A$4+$A$5+$A$6+4,INDEX(Sheet3!B:B,ROW()-$A$4-$A$5),"")))

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.

...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.