Previous Topic Next Topic

Explanation of formula used on conditional formatting

Solved/Closed
Harry - Nov 12, 2018 at 07:05 AM
 Harry - Nov 13, 2018 at 05:08 AM
Hi,

Could someone please be able to fully explain how the following formula works with regards to conditional formatting?

=AND(start_of_range<>"", NOT(ISERROR(MATCH(start_of_range,list_name,0))))

It works like a charm but I no idea how it fully works.

How can each individual cell that this conditional formatting act independently from each other when only the top left cell it referred to?

A little background to this problem it solved.

I have four lists with different names. They have a named ranged based on list title. I have a range of cells say A1:C8 that I want to apply conditional formatting to colour the name present based on what list it is in. Using the formula above inputted as: =AND(A1<>"", NOT(ISERROR(MATCH(A1,list_name,0)))).

Any help would be much appreciated.

Kind Regards,

Harry

2 replies

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Nov 12, 2018 at 12:13 PM
Hi Harry,

That is because of absolute and relative cell references based on dollar symbols in front of either the column letter or the row number or both.

You didn't use the dollar symbol in your cell reference A1, thus making it a relative cell reference. After using the formula for cell A1, Excel will check A2, but also changing the A1 to A2 in the formula.

Try changing A1 to $A$1 in the formula to get the result you were expecting (can also be done with F4 after selecting A1 in the formula).

Does that make sense?

Best regards,
Trowa
0
Answer 2 / 2
Harry
Nov 13, 2018 at 05:08 AM
Hi Trowa,

Thank you for your reply. basically I didn't realise that the absolute and relative cell ref worked with conditional formatting as well. Now it makes perfect sense.

Cheers again.

Harry
0