INDIRECT function inside AND function
 

Try using Offset.

I don't know what cell you're wanting to format but offset that cell by 0
rows and the number of columns from column G.

For example, if the cell being formatted was A13:

=AND($E13="Y",OFFSET($A13,,6)="",$M13="")

Biff

"andy62" wrote in message
...
I have the following formula as a conditional formatting test:

=AND($E13="Y",INDIRECT("G13")="",$M13="")

The INDIRECT function is testing to see if the cell in column G of the
same
row is blank. I need to use INDIRECT instead of a direct reference
because
the data in column G can get cut/pasted, and I need to hold the reference
stable. But I need to copy this conditional format down 1500 rows, so I
tried to change to INDIRECT("G"&ROW()). But it doesn't quite work,
because
the INDIRECT function tests out as "Volatile" instead of "TRUE"?? Anyone?

INDIRECT function inside AND function
 

Hey, that worked great, thanks.

So what was the flaw in my INDIRECT("G"&ROW()) approach? I tested the
function outside of the condition format and it worked fine.

"Biff" wrote:

Try using Offset.

I don't know what cell you're wanting to format but offset that cell by 0
rows and the number of columns from column G.

For example, if the cell being formatted was A13:

=AND($E13="Y",OFFSET($A13,,6)="",$M13="")

Biff

"andy62" wrote in message
...
I have the following formula as a conditional formatting test:

=AND($E13="Y",INDIRECT("G13")="",$M13="")

The INDIRECT function is testing to see if the cell in column G of the
same
row is blank. I need to use INDIRECT instead of a direct reference
because
the data in column G can get cut/pasted, and I need to hold the reference
stable. But I need to copy this conditional format down 1500 rows, so I
tried to change to INDIRECT("G"&ROW()). But it doesn't quite work,
because
the INDIRECT function tests out as "Volatile" instead of "TRUE"?? Anyone?

INDIRECT function inside AND function
 

Not sure I understand *why* this is happening but I know *what* is
happening.

The formula is being processed as an array (even though it is not an array
formula, that's just how the refedits work) and it evaluates to a #VALUE!
error:

=AND($E13="Y",INDIRECT("G"&ROW())="",$M13="")

Broken down in steps:

INDIRECT("G"&ROW())=""
INDIRECT("G"&{13})=""
INDIRECT({"G13})=""
{#VALUE!}=""
#VALUE!

Try it on the worksheet as an array and you'll see what I mean.

Biff

"andy62" wrote in message
...
Hey, that worked great, thanks.

So what was the flaw in my INDIRECT("G"&ROW()) approach? I tested the
function outside of the condition format and it worked fine.

"Biff" wrote:

Try using Offset.

I don't know what cell you're wanting to format but offset that cell by 0
rows and the number of columns from column G.

For example, if the cell being formatted was A13:

=AND($E13="Y",OFFSET($A13,,6)="",$M13="")

Biff

"andy62" wrote in message
...
I have the following formula as a conditional formatting test:

=AND($E13="Y",INDIRECT("G13")="",$M13="")

The INDIRECT function is testing to see if the cell in column G of the
same
row is blank. I need to use INDIRECT instead of a direct reference
because
the data in column G can get cut/pasted, and I need to hold the
reference
stable. But I need to copy this conditional format down 1500 rows, so
I
tried to change to INDIRECT("G"&ROW()). But it doesn't quite work,
because
the INDIRECT function tests out as "Volatile" instead of "TRUE"??
Anyone?

INDIRECT function inside AND function
 

Thanks for the explanation. Thanks to you and others I have some
unbelievable formulas in my spreadsheets (see below), and I generally have
enough sense of how they work to adjust them or combine them with other
formulas, but I don't often know exactly why they work. I probably need to
take some kind of advanced excel class . . .

Here's one of the crazier ones:
=IF(ROWS(B23:B$23)<=COUNTIF('All
Data'!$G$4:$L$1504,$B$27),CONCATENATE(INDEX('All
Data'!$C$4:$C$1504,SMALL(IF('All Data'!$G$4:$L$1504=$B$27,ROW('All
Data'!$G$4:$L$1504)-ROW('All Data'!$H$4)+1),ROWS(B23:B$23)))," (",INDEX('All
Data'!$G$3:$L$3,SUMPRODUCT((INDIRECT("'All Data'!F"&SMALL(IF('All
Data'!$G$4:$L$1504=$B$27,ROW('All Data'!$G$4:$L$1504)-ROW('All
Data'!$H$4)+1),ROWS(B23:B$23))+3&":K"&SMALL(IF('Al l
Data'!$G$4:$L$1504=$B$27,ROW('All Data'!$G$4:$L$1504)-ROW('All
Data'!$H$4)+1),ROWS(B23:B$23))+3)=$B$27)*COLUMN('A ll
Data'!$G$4:$L$1504))-7),")"),"")


"Biff" wrote:

Not sure I understand *why* this is happening but I know *what* is
happening.

The formula is being processed as an array (even though it is not an array
formula, that's just how the refedits work) and it evaluates to a #VALUE!
error:

=AND($E13="Y",INDIRECT("G"&ROW())="",$M13="")

Broken down in steps:

INDIRECT("G"&ROW())=""
INDIRECT("G"&{13})=""
INDIRECT({"G13})=""
{#VALUE!}=""
#VALUE!

Try it on the worksheet as an array and you'll see what I mean.

Biff

"andy62" wrote in message
...
Hey, that worked great, thanks.

So what was the flaw in my INDIRECT("G"&ROW()) approach? I tested the
function outside of the condition format and it worked fine.

"Biff" wrote:

Try using Offset.

I don't know what cell you're wanting to format but offset that cell by 0
rows and the number of columns from column G.

For example, if the cell being formatted was A13:

=AND($E13="Y",OFFSET($A13,,6)="",$M13="")

Biff

"andy62" wrote in message
...
I have the following formula as a conditional formatting test:

=AND($E13="Y",INDIRECT("G13")="",$M13="")

The INDIRECT function is testing to see if the cell in column G of the
same
row is blank. I need to use INDIRECT instead of a direct reference
because
the data in column G can get cut/pasted, and I need to hold the
reference
stable. But I need to copy this conditional format down 1500 rows, so
I
tried to change to INDIRECT("G"&ROW()). But it doesn't quite work,
because
the INDIRECT function tests out as "Volatile" instead of "TRUE"??
Anyone?