Conditional Formatting with a INDEX(MATCH) array formula

Hello,
I do not know if what I am trying to accomplish is possible with this formula. I have the entire formula working in a normal cell to return a TRUE/FALSE based on criteria. I cannot figure out how to translate this formula into the conditional formatting.

In the attached document:

ExecView!F8:F16 needs the conditional formatting
ExecView!J8 contains contains the example of the formula I want to use for the conditional formatting

Please Login or Register  to view this content.

ExecView!K8 contains only the portion of the above formula that is the variable to ensure the INDEX(MATCH) is functioning

Please Login or Register  to view this content.

Hopefully someone can assist on how to accomplish this, maybe with a different formula to get the same results.
Thanks
B

Hi,

I think it has something to do with use the Table format references such as [Reserve(T3)], [SITE] and [MAN].

Change them to column references.

Please Login or Register  to view this content.

However, referencing the entire column will really slow things down.

You can adjust them to area specific. Such as:

Please Login or Register  to view this content.

Though this can slow things down if you have a lot of them. Try a SUMIFS instead.

Please Login or Register  to view this content.

Hope this is helpful.

Cheers

@southward

Thanks for the input but I like to keep my formulas as dynamic as possible (I should have mentioned that in my first post).

I try to keep my formulas very dynamic and use variables instead of references. I have found that to be best when dealing with sheets that do and will change structure frequently. I know it might not be the ideal way for best performance but, I have found it to the best way when dealing with users who do not know Excel very well.

Is there any other way to accomplish this formatting?
Thanks
B

You can use Dynamic Named Ranges that will automatically grow and include any new data.

Here is one of the DNR's I used in the attached example.

Please Login or Register  to view this content.

The DNR names I used are "Man", "ResT3" and "Site".

This formula works in the conditional formatting.

Please Login or Register  to view this content.

Hope this works for you.

Cheers