Conditional Formatting issues

Question

Hi there,

I wonder if someone can help with conditional formatting in terms of absolute/relative cell formulas.

I am creating a spreadsheet which summarises records held for patients. There is a ton of conditional formatting to make certain cells turn red/orange, for example, depending on whether action is required, giving a quick visual reference for each case.

I stupidly thought I could just copy and paste this record to repeat through the sheet, but the conditional formatting is referencing the first record I made.

When I remove the "$" from the conditional formatting formulas, the rule no longer works, with some cells responding and some not. I removed all the conditional formatting and started to build it all back up from scratch but I'm still having the same issues.

For example:

=C9=""

formats the whole record to show as white fill with black writing, to indicate that there's no patient number in cell C9, therefore the record is inactive and waiting for entry. When a number is placed in C9, the conditional formatting should then reveal the colours and make the entry appear 'live'. This works fine when C9 is in the formula as $C$9, but when just C9, it doesn't work, although one cell seems to change.

I removed the $ signs from the 'Applies to' field, but they filled themselves back in.

This all seems really odd to me, can someone help??

Answer 1

When conditional formatting is applied to a range like a column and relative addressing is used, the formula entered should be as if only applying to the first cell of the "Applies to" range.

Example: If the "Applies to" range is column C, then the first row in any formula will be 1 so row 1 is used in the formula for Conditional Formatting. Most times when applying the formula to the cells of row 1, it will be the column headers and generally they will not meet the criteria so no problem. If the column headers are a problem because they will meet the criteria, it might be necessary to make the "Applies to" range from row 2 and down to whatever row is required and then the formula is applied as if C2 is the first cell. Excel applies the formula in the relative format to the remaining cells in the column.

If still having problems, then please post a screen shot of the data and explain what ranges need to be formatted and what condition must be met to apply the formatting. (If any sensitive data like people's names in the data, then make a copy of the worksheet and replace sensitive data with dummy data like Name 1, Name 2 etc.

Tip for screen shots. Ensure that you include the column headers and row numbers, even if it means you need to hide some rows and columns to make the screen shot viable for size.

Answer 2

Thanks OssieMac, really appreciate your message.

Here's an example.

So, I want all the blue coloured cells to change to a different colour depending on who the case is assigned to. So for example, here, it should all be yellow for Nicola.

It works if there's a $ sign before both the column and the row (i.e., if the formula is =$D$16="Nicola"). What I wanted to be able to do was to copy this blank record, and paste it below with the same conditional formatting relative to that record, but by doing this, the subsequent records refers to D16, rather than the 'Assigned to' for that record.

Is this even possible?!

Answer 3

Thanks Tom. It is now well explained. I'll need to experiment a bit to come up with the answer, but I will work on it and hopefully I can resolve the issue.

However, is there any reason not to apply the colour to the entire range B9:F17?

At the moment, I can't think of any method of achieving your desired result without editing the formula for each "Applies to" range because it will be a different name to match for each range.

Answer 4

@Tom,

Unfortunately, I have not been able to resolve your problem. Copying the Conditional Format area and pasting changes the "Applies to" range to the new range. However, it does not change the range to match in the formula section.

In any case you will need to change the name to match so I suggest that at the same time you edit the range, still using absolute addressing.

Are you aware that to edit the formula, it is necessary to click in the formula field and then press F2 to enter edit mode. Some laptop computers actually have 2 different functions assigned to the F1, F2 keys etc. This can require the user to hold the Fn key and then press F2 for the function key to work. This can also be changed during the boot process and the key function can be reversed so it is the alternative function on the key that is the default without using the Fn.

Answer 5

Thank you OssieMac, really appreciate the help.

It seems that to get round this, I would need to create a conditional formatting rule for each individual cell which in time, as records are duplicated, might be difficult for the spreadsheet to handle I guess?