Using a formatted cell to give a value for gantt charts

[deeva]

We are drawing monthly gantt charts using excel, where for each activity users colour fill cells across a row in the corresponding monthly titled columns. I would like to export this data into another program, and need the start and end dates for each of these activities. Is there a formula or VBA command i can use to get this data?

thanks

 

[Domski]

Hi & welcome,

Not really what excel was designed for. You could do it if you were entering some kind of value in the first and last cell at the start and end of each activity using something like Index and Match, Maybe S to signify the start and E the end.

Something like:

Book4
	A	B	C	D	E	F	G	H	I	J	K
1	Date	01/10/2009	02/10/2009	03/10/2009	04/10/2009	05/10/2009	06/10/2009	07/10/2009	08/10/2009	09/10/2009	10/10/2009
2	Task 1	S			E
3	Task 2			S			E
4	Task 3				S				E
5
6		Start	End
7	Task 1	01/10/2009	04/10/2009
8	Task 2	03/10/2009	06/10/2009
9	Task 3	04/10/2009	08/10/2009
Sheet1

Formula in B7 copied down: =INDEX($B$1:$K$1,MATCH("S",$B2:$K2,0))

Formula in C7 copied down: =INDEX($B$1:$K$1,MATCH("E",$B2:$K2,0))

Hope it helps,

Dom

 

[deeva]

this was fabulous, thanks!!! although it has a manual element involved in going through to change the start and end cells, it at least helps limit the opportunity for human error having to manually update a date.

might keep it out of my VBA macros for now - not sure if the world is ready for my hair pulling on that one!

thanks

 

[deeva]

i'd also like to add that this approach has helped me with another problem that has been bugging us for a while, two birds with one stone!