Using a formatted cell to give a value for gantt charts

deeva

New Member
Joined
Oct 20, 2009
Messages
3
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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
ABCDEFGHIJK
1Date01/10/200902/10/200903/10/200904/10/200905/10/200906/10/200907/10/200908/10/200909/10/200910/10/2009
2Task 1SE
3Task 2SE
4Task 3SE
5
6StartEnd
7Task 101/10/200904/10/2009
8Task 203/10/200906/10/2009
9Task 304/10/200908/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
 
Upvote 0
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! :nya:

thanks :)
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,705
Members
453,748
Latest member
akhtarf3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top