Conditional Formatting in Excel

Dell / Gxe
January 4, 2009 at 13:01:22
Specs: Windows XP, 200
I am creating a shared sign-in sheet that shows who is still waiting to be seen.
I am using the following function:
The columns appear as follows:
B column (Name of Guest)
C column (Time In)
D column (Staff Met)
E column (Pick up Time)
F column (Notes)
G column (Requested Staff)
H column (Status)

The conditional formatting that is applied to column H is:

cell value | equal to | ="Waiting

cell value | not equal to | ="False"

This works fine, but I would like to be able to have the oldest sign-in time that is "still waiting" to be highlighted. As soon as the oldest sign-in time is picked up (signed out), then the next oldest sign-in time will be highlighted.

Thank you

See More: Conditional Formatting in Excel

Report •

January 6, 2009 at 09:15:31
this is a bit tricky!

Got it to work using the DMIN function.

Note this formula requires you to have headings at the top, and you'll need to hide some columns and rows that I'll be using. I'm assuming that your list starts in cell B1, and is 100 lines long including header rows.

So in cell A1 (insert a column and row if needs be) type in "Status"
In cell A2 (insert another row) type in "WAITING"
In cell B2 (insert another column) type in

=DMIN($D$3:$J$100,"time in",$A$1:$A$2)

this will give you the lowest time that exists that matches the criteria (namely, that are still waiting).

So leave in your existing conditional formatting but you need to have a new first condition:

cell formula | equal to | =AND(J4="WAITING",E4=$B$2)

Note this is the conditional formatting on cell J4 (status, first row)...obviously you'll need to amend the row number and possibly the columns depending on how your data is set out.

You can hide columns A and B, and rows 1 and 2 or just put them outside your print area.

Apologies it's a bit long-winded, hopefully someone can come up with a better solution!

Report •

January 17, 2009 at 08:47:21
Thanks for your reply.
Still having problems getting this to work.
Are the following changes correct?

Cell A1 has the word Status
Cell A2 has the word WAITING
Cell B2 has the formula =DMIN($D$3:$J$100,"time in",$A$1:$A$2)
New first condition of cell formula | equal to | =AND(J4="WAITING",E4=$B$2)

The column headings are as follows:

D3 is Name of Guest
E3 is Time In
F3 is Staff Met
G3 is Pick up Time
H3 is Notes
I3 is Requested Staff
J3 is Status

In cell B2 I get .333333
I am still not able to highlight the oldest guest that is waiting.

Report •

Related Solutions

Ask Question