Home 
Search 
Today's Posts 
#1




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? 
#2




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? 
#3




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 news 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? 
#4




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 news 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? 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Can I use a Reference inside a SUMIF or COUNTIF Function?  Excel Worksheet Functions  
use the OR function inside an IF function  Excel Worksheet Functions  
HELP NEEDED FOR IF Function Inside a Macro  Excel Worksheet Functions  
Nesting Networkdays function inside and If function  Excel Worksheet Functions  
SUMIF Function Inside SUMPRODUCT Function  Excel Worksheet Functions 