Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 23rd 06, 04:28 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,688
Default 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   Report Post  
Old September 23rd 06, 05:08 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 158
Default 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   Report Post  
Old September 23rd 06, 05:52 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,688
Default 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   Report Post  
Old September 23rd 06, 07:20 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 158
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I use a Reference inside a SUMIF or COUNTIF Function? Albie Excel Worksheet Functions 11 May 14th 10 04:26 PM
use the OR function inside an IF function Inbar Excel Worksheet Functions 10 June 18th 06 05:55 AM
HELP NEEDED FOR IF Function Inside a Macro Sandi Excel Worksheet Functions 1 June 2nd 06 04:14 AM
Nesting Networkdays function inside and If function Addison Excel Worksheet Functions 2 April 13th 06 08:04 PM
SUMIF Function Inside SUMPRODUCT Function Abdul Waheed Excel Worksheet Functions 17 September 19th 05 04:24 PM


All times are GMT +1. The time now is 06:10 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017