HELP! Someon's gotta know this

[Guest]

I have a spreadsheet with all of our clients and the services we deliver to
each client. I have it all set up with dates, conditional formatting, IF
functions, point values, etc.

When we get a new client and add them to the spreadsheet, it adds cells for
all of the services that we WILL deliver to the client. If, after a certain
amount of time, depending on the service, the service is not delivered to the
client, it becomes overdue.

I want to see if there is a way to turn the cell red if no data has been
entered into the cell after a month (31 days).

Any suggestions?

 

[Guest]

Use Conditional Formatting (Formula Is):

If the cell is blank AND the current date exceeds the posted date by more
than 31 days, format the cell to RED

 

[Guest]

Well, here's what I figured out, but it doesn't work.

=($A$1>NOW())+("")

Any critiques?

- Ethan

 

[Mark Lincoln]

If the cell to be formatted is B1 and the date to compare to is A1,
then enter this formula in the Conditional Formatting dialog for B1:

=AND(ISBLANK(B1),(NOW()-A1)>31)

Set the CF cell format to red, and you should be set. This will cause
B1 to turn red if it is blank and more than 31 days have passed from
the date in A1. Use the Format Painter to copy the format down as far
as you need.

Mark Lincoln

 

[Guest]

In the Conditional Formatting pane

Cell Value is: less than ="Today() - 31"

 

[Guest]

Ignore my previous reply. It does not work.

 

[Guest]

Well, thanks! That almost worked, but from what you gave me, I figured this
out and it works great:

=AND(ISBLANK(B1),(NOW()-31<A1))

Thanks for all your help!!!

- Ethan

 

[Guest]

Ok, new problem:

None of the new cells are referenced to a cell with a date, say the date the
client was added.

My macro currently inserts a row and formats the appropriate cells. I can
easily modify it so it puts the date the client is added into a cell, but if
I set the above conditional formatting into my macro, it only references it
to a set cell. Does that make sense?

I'm caving in on myself here....

 

[Mark Lincoln]

I want to see if there is a way to turn
the cell red if no data has been
entered into the cell after a month (31 days).

This tells me that you wish the cell to turn red if it is blank after
31 days have passed. Your formula turns the blank cell red
immediately, and if it remains blank it will remain red until the day
after 31 days have passed, when it will revert to its normal
formatting.

However, if your formula works for you, it works for me!

Mark Lincoln

 

[Guest]

Hmmm...If the date in A1 is over 31 days ago, it turns the cell red, but any
date earlier than 31 days leaves it with no fill color.

Anyway, I'm having a problem with it still. The way the formula is written,
it will always reference that ONE cell. I have multiple clients I add and
need it to reference the cells for THOSE clients. Any suggestions?

 

[Mark Lincoln]

Copying the format down (using Format Painter) or copying the cells
(by dragging down) will copy the conditional formats as well. Because
the CF formula was not written with absolute references, the newly-
formatted cells will look to the same relative position as their
predecessors. So if B1 is referencing A1, B2 will reference A2, and
so on.

Mark Lincoln

 

[Guest]

Very true, and that much I understand. However, some services are not
delivered to some clients. If I were to fill down, the same services that are
not delivered are not necessarily not delivered to the new client. This
leaves me with having to manually enter in the conditional formatting into
the new cell(s).

I'm trying to make this as laymen as possible for other users in the office.
If everything is automatic, it's MUCH easier for me to phase out of having to
watch over it like a newborn.

If there's no way to do it, it's not a big deal. I just need the peace of
mind.

- Ethan

 

[Guest]

I'm not sure if this is what you're getting at, but in VBA you can refer to
cells by specific address (ex., in C1: "=A1+A2+B1") or by relative address
(same ex: "=RC[-2]+R[1]C[-2]+RC[-1]") To use the relative address, use
..FormulaR1C1 = ...

drhalter

 

[Mark Lincoln]

I'm fairly sure it can be done, but I don't have enough information
about the layout of your workbook and if I guess at it and offer a
solution based on my guess I'm sure I'll have you very confused in
short order.

Can you describe what a typical record looks like? Is it in a single
row? Most important, how do you specify which services are to be
provided to a client and which services have actually been provided?
The answer to this last question will determine how you can use CF
effectively.

Mark Lincoln