Excel help needed
TryinToGetPaid
13-02-2008 08:03:02
I have a spreadsheet that I am tracking offices that I audit. I am tracking what date the audit is sent out and if I get it back I am placing a DONE in the column next to the column with the date. For a visual
2/8/08 DONE
2/1/08
2/3/08 DONE
ETC.
What I want, is the row that does not have DONE in column 2, if the date reaches past 2 weeks from the current date, it turns red and lets me know I need to contact that office. Conditional formatting would work but I can not do If Column D = DONE then Column C = format red. Any ideas?
CollidgeGraduit
13-02-2008 08:17:24
Yep, I can do that.. gimme 10 mins and I'll have something for you
TryinToGetPaid
13-02-2008 08:19:58
Appreciated to the max.
CollidgeGraduit
13-02-2008 08:20:52
On second thought, this is pretty complicated. I'll keep tinkering around, but it'll take more than 10 mins
TryinToGetPaid
13-02-2008 08:28:50
Time extended.
doylnea
13-02-2008 08:36:27
[quotee42a0ea512="TryinToGetPaid"]Conditional formatting would work but I can not do If Column D = DONE then Column C = format red. Any ideas?[/quotee42a0ea512]
Why not, I believe you just need to set multiple conditions. I did this for LC's offer comparison spreadsheet?
TryinToGetPaid
13-02-2008 08:41:03
But if DONE is in Column D I do not want Column C to be formatted. I only want it formatted if there is no DONE in Column D.
CollidgeGraduit
13-02-2008 08:48:29
Got it!
If A1 is your date, and B1 is your "DONE".. Click on A1, and go to Conditional Formatting, and change the Condition1 dropdown to Formula Is. Then copy and paste this in
=IF(AND(TODAY()>=A1+14,B1<>"DONE"),1,0)=1
That should do the trick.
Edit I see you aren't necessarily using A1 and B1.... but the formula should be easy enough to tailor to your needs.
manOFice
13-02-2008 08:49:59
[quote99508217bb="CollidgeGraduit"]Got it!
If A1 is your date, and B1 is your "DONE".. Click on A1, and go to Conditional Formatting, and change the Condition1 dropdown to Formula Is. Then copy and paste this in
=IF(AND(TODAY()>=A1+14,B1<>"DONE"),1,0)=1
That should do the trick.
Edit I see you aren't necessarily using A1 and B1.... but the formula should be easy enough to tailor to your needs.[/quote99508217bb]
CG FTW!
TryinToGetPaid
13-02-2008 08:51:37
Negative. liI changed A to C and B to Dli All it did was change all formatting of the dates to the red/bold. Appreciate the help though.
Edit It now isn't doing all the dates, instead it appears to be random. Some rows without DONE have red like its supposed to, others without done are not formatted and some with DONE are formatted......
hehehhehe
13-02-2008 08:59:27
CG's formula seems perfect to me. Did you add the $sign in front of the cell IDs like this (for row 1, and assuming date is in column A)?
=IF(AND(TODAY()>=$A1+14,$D1<>"DONE"),1,0)=1
You said you replaced column A with C but isn't C the column you want the color changed in?
Are all of the columns formatted correctly (either text or dd/mm/yyyy date)?
TryinToGetPaid
13-02-2008 09:02:28
Yes they are all formatted equal. I am going to upload the spreadsheet so someone can view it.
CollidgeGraduit
13-02-2008 09:10:04
I used your spreadsheet, and this formula, works like a charm.
=IF(AND(TODAY()>=C9+14,D9<>"DONE"),1,0)=1
hehehhehe
13-02-2008 09:12:56
Yeah, I tried it and CG's formula worked perfectly.
Use this in row 2 if you're going to copy it down the column
=IF(AND(TODAY()>=$C2+14,$D2<>"DONE"),1,0)=1
It won't work without the $sign like I said before.
TryinToGetPaid
13-02-2008 09:16:26
It works. I forgot to put the 2 instead of one. I appreciate the help from both of you. If I had money in my PP I would send it -- but I don't... just ask Tyler for extra money on your checks he sends you every month. +Karma for both of you. (more for CG of course)
CollidgeGraduit
13-02-2008 09:27:48
[quote2ed0127586="TryinToGetPaid"]If I had money in my PP[/quote2ed0127586]
tee hee
TryinToGetPaid
13-02-2008 09:29:30
I am glad that got a rise out of you.
Did you free hand that formula? I am decent in Excel but free handing a formula is something I have not been able to sit down and practice with.
CollidgeGraduit
13-02-2008 09:34:19
Yep I did. In my last position at my current employer, there was a ton of sifting through spreadsheets manually.. so I got a lot of practice with formulas to help speed things up.
TryinToGetPaid
13-02-2008 09:35:55
Yeah, I work with spreadsheets constantly (as do the other application analysts) and just me knowing how to conditional format has the entire department in awe.