Range-defined Array using Indirect Formula not working in CF (Conditional Formatting)

[abskiey]

Hello guys!
Need your support!

I have an important formula that needs to run in CF.

=1=MAX(INDEX(Q:Q,ROW()):INDIRECT(ADDRESS(ROW(),COLUMN())))

Above used to format cells by checking the maximum value of 1 in a defined range of cells (in a single current row) from Column Q up to the current Column.

Note that ":" colon represent a range which does not work in Excel 2016 CF. Please help how to do alternative methods.

Your immediate support is very much appreciated!

 

[RoryA]

Welcome to the forum.

If you put that formula as the refers to value of a defined name (using Name manager on the formulas tab), you should be able to use that name as the validation source. Since you have relative references in the formula, make surnames you select the correct cell before creating the name.

 

[abskiey]

Welcome to the forum.

If you put that formula as the refers to value of a defined name (using Name manager on the formulas tab), you should be able to use that name as the validation source. Since you have relative references in the formula, make surnames you select the correct cell before creating the name.

Thanks for answer. However could you please support it with screen shots?

 

[RoryA]

Not from my work computer, I'm afraid.

First select a cell in the column you intend to apply this to.
On the Formulas tab, click Name Manager, then New...
Enter a name for the formula, such as CF_formula, and in the refersto box paste your formula above and then press OK.
Now for your CF formula just use:
=CF_formula

(or whatever name you gave the formula).

 

[abskiey]

Not from my work computer, I'm afraid.

First select a cell in the column you intend to apply this to.
On the Formulas tab, click Name Manager, then New...
Enter a name for the formula, such as CF_formula, and in the refersto box paste your formula above and then press OK.
Now for your CF formula just use:
=CF_formula

(or whatever name you gave the formula).

Oh it helped me. It's working fine. However it's not working with the data validation custom.

Thanks a lot anyway!

 

[abskiey]

Not from my work computer, I'm afraid.

First select a cell in the column you intend to apply this to.
On the Formulas tab, click Name Manager, then New...
Enter a name for the formula, such as CF_formula, and in the refersto box paste your formula above and then press OK.
Now for your CF formula just use:
=CF_formula

(or whatever name you gave the formula).

By the way I have a new post about converting a formula to fix value automatically based on real time:

https://www.mrexcel.com/forum/excel...fix-values-automatically-based-real-time.html

Maybe you have an idea.

Thanks!