Solved

conditioning formatting

Posted on 2014-01-22
16
211 Views
Last Modified: 2014-02-01
Folks

I have a conditional format that looks at a start date in columm b and end date in c and
checks out the week numbers, if this corresponds in a weeknumber range d1 to bc1 then it highlights the cells yellow ,like so

•=AND(D$1>=WEEKNUM($B1),D$1<=WEEKNUM($C1))

what I would like is to also have have condition format the cell with a letter "X" but also, how can I build functionality that if I remove the "X" for that cell , that the code goes and changes either the start date or end date with the new revise date range

all help will do, below is the atatched sample which I already have
gannt.xlsx
0
Comment
Question by:rutgermons
  • 9
  • 6
16 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39802315
Well put this formula in D2 and drag it right most and down as much as you want.

=IF(AND(D$1>=WEEKNUM($B2),D$1<=WEEKNUM($C2)),"X","")

chk the file
gowflow
gannt.xlsx
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39802907
if I remove the "X" for that cell , that the code goes and changes either the start date or end date with the new revise date range

How would it know what the revised date would be since each "X" represents a week number?  If you remove the first "X" in a row where the start date is 1/4/2014, the revised start date could be anywhere between 1/5/2014 and 1/11/2014 since that range represents week number 2.
0
 

Author Comment

by:rutgermons
ID: 39802925
and "x"  specifies a week so it should subtract a week if removed, im conscious it might not be possible, but keen to understand if it is
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39803135
Ok now I understand what you want.

Pls try this file and play with the X the way you want and see the colors changes as well as the dates. Is this what you want ???
gowflow
ganntV01.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39815718
Any chance to have tried the above solution posted ?
gowflow
0
 

Author Comment

by:rutgermons
ID: 39815838
doesnt work, i would like the code to work both ways, when changing the start /end dates it should set the highlighting with marking the calls yellow/x if between the spcified dates and vice versa, if I remove the x then it should update the dates itself
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39817696
ok now got it !!!
You then need a mixture of macro as well !!! hmmm :)

here you are
Enjoy

I hope this does it ... play with the x .... and put dates ...
just make sure macros are activated prior to loading the file
let me know
gowflow
ganntV02.xlsm
0
 

Author Comment

by:rutgermons
ID: 39817706
nearly nearly, i would like all the weeks filled with a "x" so if I remove the x from week 5 then it only recalculates the end date to week 4
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 29

Expert Comment

by:gowflow
ID: 39817716
well even if no xxxx try what you just said and see the result
if x in wk4 then x in week 10
if you want shorter remove x from week 10 then only week4 is yellow and put where you want.

similarly if you put x in week 2 it will remove week 4 and you will have 2 to 10

gowflow
0
 

Author Comment

by:rutgermons
ID: 39818230
did see if I set the end date to an earlier finish date the highlighted yellow works and adjust's accordingly but the x in the orginal end date still displays
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39818332
give me a specific example so I see where is what is not functioning as I tested quite long and it was working here.

What version of excel you have I developed this in Excel 2007 sometimes Excel 2010 have weired behaviours.

Let me know
gowflow
0
 

Author Comment

by:rutgermons
ID: 39818437
line 9, set end date to 5/5/14 (x keeps showing on 5/1/14 ,show only reflect on 5/5/14 for end date

line 9, set end date back to 5/2/14 (x keeps showing on 5/5/14 ,show only reflect on 5/2/14 for end date
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39818612
Your correct 100%. Usually users are the best troubleshooters !!!
Does this makes it better ?
gowflow
ganntV03.xlsm
0
 

Author Closing Comment

by:rutgermons
ID: 39820155
thanks GowFlow for your hardwork, I took the easyroute and had u do the work, thanks for letting me learn from you

i may log a follow up call on this today so enhance it further
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39820167
No problem tks your comments. Pls post a link in here and will b glad to assist.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39826960
I don't get it with your new question what are you trying to achieve ?? it is not clear !!! isn't it the same as this one with the x ??

If this question is done with  please close it prior to opening new questions with the same scoope and subject !!
gowflow
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

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

20 Experts available now in Live!

Get 1:1 Help Now