Solved

Conditional format help/gannt plan

Posted on 2014-01-30
22
248 Views
Last Modified: 2014-02-05
folks

see sample attached, I have a gantt chart and I need the conditional format to change based on an amendment on the gantt tracking bar itself

refer to line 7 notes in the sample sheet attached

good luck
ganntV04.xlsm
0
Comment
Question by:rutgermons
22 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39820198
HI,

What do you mean?

regards
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39823760
Is this what you want ?
gowflow
ganntV04.xlsm
0
 

Author Comment

by:rutgermons
ID: 39824560
cheers gowflow, yes exactly,superb, one thing though, if I copy and past start and end dates the x values dont appear

any ideas?

this is
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39825332
yes for sure when you copy paste it does not activate change events !!!
what else you want a drink on the moon and hv the macro fires up the space shuttle to get back to earth /???

just kidding !
gowlfow
0
 

Author Comment

by:rutgermons
ID: 39825334
I do see that modifying the start and end dates is a wee bit buggy
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39825340
pls clarify your quotes
give examples.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39825438
Yes I reviewed it totally and check this one.
gowflow
ganntV05.xlsm
0
 

Author Comment

by:rutgermons
ID: 39825973
goflow, try and copy past the start/enddates from tasks 1 and 2 to lines 24/25 for example, the x values dont appear

my end goal is to conenct this to an oracle db where a query gets extracted into these columns and here I will have the same problem where the x doesnt appear

thoughts?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39826009
I like your challenges they push me more and more to refinement !!! although this seems to be very easy and conceivable when you post it first it is quite intricate as to programing it. What I like most about it is that it is very practical and easy to use and can have quite a few use to it !!!

Sometimes all we need is just ideas !!
great idea from you anyway.

Pls test it thoroughly
Regards
gowflow
ganntV06.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39826346
Hi again
I liked your idea so I went ahead and made it more realistic .. check out the below picture if you like it then I will b glad to share it with you by posting a new question when we get done with this one.
One Row 1 you have the Months
Row 2 is the first day of the week
I also highlighted every Quarter with a different color in weeks.

it is all flexible with today's date so if your next year it will automatically align to new data.
Regards

Month Daygowflow
0
 

Author Comment

by:rutgermons
ID: 39826421
will do, i am not sure though on the conditional formats, some calls have 2 formulas and some have 1, shouldnt this be consistant?

if i copy the start and end dates from row 25 to rows 27 to 31 other cells get highlighted not even in rows 27-31

thoughts?

in terms of next steps, things such as percentage complete would be handy, to track project progress
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:rutgermons
ID: 39826535
i logged a new question for th dynamic dates, its logged in the excel group
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39826602
Pls put a link of the question here
gowflow
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39828236
Wow, this all looks similar to a Leave Log I made almost 10 years ago when I was in the Navy.  I still kept a copy so here's what it looks like:Leave LogI just now revised it to highlight the weekends and to give the highlighted days a 3D feel.  This just uses conditional formatting as well so there is no macro to update the days by adding or removing an "X".  However I do like the idea and I think I'll play with my version a bit.  Since mine is set by days and not by weeks I could only show 8 months at the most because at the time Excel had a limit of 255 columns.

One thing I think you need to do to make yours more dynamic is to base your date range on the dates you enter rather than from January to December.  Otherwise, how would you show the tasks that go from this year to the next year.  In my case, I took the minimum date of the "FROM" column and subtracted a week to get the first day my chart would display.
0
 

Author Comment

by:rutgermons
ID: 39828363
gowflow

 i am not sure though on the conditional formats, some calls have 2 formulas and some have 1, shouldnt this be consistant?

if i copy the start and end dates from row 25 to rows 27 to 31 other cells get highlighted not even in rows 27-31

thoughts?
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39829152
ok fine use this one instead. but believe Ver 07 in the next question is what you want. It has all the code that is in this one + the new formatting. This one will work fine as well.
gowflow
ganntV06A.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39829479
TKs glad we finally nailed it.
I will keep this question monitored case you have new questions pls copy paste the address of the new question in here so I could help you in it case you need my assistance.
gowflow
0
 

Author Comment

by:rutgermons
ID: 39829489
sure, im thinking of adding 2 columns for actual start and end date to really track the project against the planned start and end dates, somehow be able to overlay the condition in a transparent colour so one can track it in the gantt,that would be cool,
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39829508
ok no prb pls put a link in here and in the new quest say it related to this one
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39835253
Any news on your development ? is the file working fine any help needed ?
gowflow
0
 

Author Comment

by:rutgermons
ID: 39835347
seems ok but if we insert new rows the x values dont get added, it seems a rogue condition is doing this which I have now removed off the sheet

will add new spec in new call soon
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39835371
no problem ! don't forget to put  link in here as soon as you post the question or else cannot attend (that is for sure if you need my help)
gowflow
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now