gantt question (diagonal candidate?)

ercstric · Mar 12th, 2002, 09:00 PM

i am using the mullins steel boat example(conditional formatting) as the base for the following:
code start end 19:00 19:10 19:20
48 20:00 20:30
39 20:30 20:45
31 20:45 21:50
39 21:50 22:00
43 22:00 22:20
where a user enters a code and the the start and end time, then bars are highlighted corresponding with the block of time represented (using the conditional formula =AND(D$1>=$B2,D$1<=$C2)). what i want is for the user to enter a coded start and end and then display the corresponding time block on a single line, color coded by code. so mabey code 31 is yellow, and 48 is green. the times will not overlap, but there may be gaps between one codes end and the next sequential start.

thanks,
eric

Chris Davison · Mar 13th, 2002, 01:03 PM

not sure what the "diagonal" is that you're referring to, but the Gannt chart uses 3 conditions to plot the bars

plotting them in colours according to code would mean a fourth condition, which exceeds the amount of conditions in conditional format

However, I believe the many VBA experts may be able to code a way around this, so I'll chuck this post back to the top for ya

Hope this helps, albeit probably negatively
Chris

ercstric · Mar 13th, 2002, 04:43 PM

to clarify, i want users to enter codes (like 31,48,34,39,43 etc.) with a start and end time, and have it plotted on a single time line, with each code being a unique color

Chris Davison · Mar 13th, 2002, 04:54 PM

yup, your bar in the chart uses up 3 conditions

>= start time
> start time and < end time
<= end time

conditional format only allows 3 conditions

further colouring any bar based on a fourth exceeds this capacity

(ummm, I think)

I've read that this can be overcome via VBA but my knowledge of VBA is about as reliable as a Zimbabwean Election Co-ordinator....

[ This Message was edited by: Chris Davison on 2002-03-13 15:55 ]

ercstric · Mar 13th, 2002, 08:40 PM

common guys.... no one has any ideas??

[ This Message was edited by: ercstric on 2002-03-13 19:40 ]

ercstric · Mar 14th, 2002, 05:48 PM

any vba ninjas out there got some code for this?

ercstric · Mar 14th, 2002, 06:43 PM

i am open to any other formats that could accomplish the single line gantt. so if you are holding back because of my original format -- DONT!

eric

ercstric · Mar 14th, 2002, 06:43 PM

.

[ This Message was edited by: ercstric on 2002-03-14 17:44 ]

Ivan F Moala · Mar 14th, 2002, 11:27 PM

Emailed your workbook.....note code
only applicable to the range you had set out
in the sheet......

HTH

Ivan

Chris Davison · Mar 15th, 2002, 12:56 PM

Eric,

I do have a messy workaround that is non-VBA which satisfies as many colours as you have in your background pattern palette (and thus around 40 of your codes) but it's a real pain in the backside.

in the very unlikely occurance of Ivan not being able to sort you out, repost and I'll type it all out.

it's basically extending conditional format beyond the 3 normal colours but is a real last resort

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 12th, 2002, 09:00 PM	#1
ercstric New Member Join Date: Feb 2002 Posts: 16	i am using the mullins steel boat example(conditional formatting) as the base for the following: code start end 19:00 19:10 19:20 48 20:00 20:30 39 20:30 20:45 31 20:45 21:50 39 21:50 22:00 43 22:00 22:20 where a user enters a code and the the start and end time, then bars are highlighted corresponding with the block of time represented (using the conditional formula =AND(D$1>=$B2,D$1<=$C2)). what i want is for the user to enter a coded start and end and then display the corresponding time block on a single line, color coded by code. so mabey code 31 is yellow, and 48 is green. the times will not overlap, but there may be gaps between one codes end and the next sequential start. thanks, eric

Mar 13th, 2002, 01:03 PM	#2
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	not sure what the "diagonal" is that you're referring to, but the Gannt chart uses 3 conditions to plot the bars plotting them in colours according to code would mean a fourth condition, which exceeds the amount of conditions in conditional format However, I believe the many VBA experts may be able to code a way around this, so I'll chuck this post back to the top for ya Hope this helps, albeit probably negatively Chris

Mar 13th, 2002, 04:43 PM	#3
ercstric New Member Join Date: Feb 2002 Posts: 16	to clarify, i want users to enter codes (like 31,48,34,39,43 etc.) with a start and end time, and have it plotted on a single time line, with each code being a unique color

Mar 13th, 2002, 04:54 PM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	yup, your bar in the chart uses up 3 conditions >= start time > start time and < end time <= end time conditional format only allows 3 conditions further colouring any bar based on a fourth exceeds this capacity (ummm, I think) I've read that this can be overcome via VBA but my knowledge of VBA is about as reliable as a Zimbabwean Election Co-ordinator.... [ This Message was edited by: Chris Davison on 2002-03-13 15:55 ]

Mar 13th, 2002, 08:40 PM	#5
ercstric New Member Join Date: Feb 2002 Posts: 16	common guys.... no one has any ideas?? [ This Message was edited by: ercstric on 2002-03-13 19:40 ]

Mar 14th, 2002, 05:48 PM	#6
ercstric New Member Join Date: Feb 2002 Posts: 16	any vba ninjas out there got some code for this?

Mar 14th, 2002, 06:43 PM	#7
ercstric New Member Join Date: Feb 2002 Posts: 16	i am open to any other formats that could accomplish the single line gantt. so if you are holding back because of my original format -- DONT! eric

Mar 14th, 2002, 11:27 PM	#9
Ivan F Moala MrExcel MVP Join Date: Feb 2002 Location: Auckland, New Zealand Posts: 4,209	Emailed your workbook.....note code only applicable to the range you had set out in the sheet...... HTH Ivan

Mar 15th, 2002, 12:56 PM	#10
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Eric, I do have a messy workaround that is non-VBA which satisfies as many colours as you have in your background pattern palette (and thus around 40 of your codes) but it's a real pain in the backside. in the very unlikely occurance of Ivan not being able to sort you out, repost and I'll type it all out. it's basically extending conditional format beyond the 3 normal colours but is a real last resort