If Function true value in Red

[donnaK]

I have written an IF function where the true value is a Lookup function. I
would like either the cell background or the text to format in Red if the
Lookup portion of the IF function is utilized (this would be the value if
true portion). I cannot use Conditional Formatting as my function refers to
different workbooks. Correct?

 

[Frank Kabel]

Hi
should be possible. Post your complete formula (one way would be to check
only the IF condition is valid. Another way: place the IF function in a
helper cell and refer to this cell)

 

[Gord Dibben]

donna

You can use references to other sheets or workbooks if you use a defined name.

From Chip Pearson's site...............

"Using Defined Names In Conditional Formatting

As noted above, custom functions in Conditional Formatting cannot reference
cells in other worksheets in the same workbook, and cannot reference cells in
other workbooks. However, you can get around this limitation by using
defined names. Create a defined name which refers to the list in the other
workbook or worksheet, and then use that name in your custom function.

For example, suppose you want to make cell A1 on Sheet1 red if that cell's
entry is not found on a list on Sheet2, cells B1:B10. If you tried to use the
formula =COUNTIF(Sheet2!$B$1:$B$10,A1)=0 as your formula, you would receive an
error message from Conditional Formatting. To get around this error, create a
defined name called MyList which refers to the range =Sheet2!$B$1:$B$10 and
use the name in your custom formula:

=COUNTIF(MyList,A1)=0

URL to site....... http://www.cpearson.com/excel/cformatting.htm

Gord Dibben Excel MVP

 

[donnaK]

=IF('[BN MASTER LIST.xls]BN MN RATES'!$D$19<=0,(LOOKUP($C15,'[BN MASTER
LIST.xls]BN Rates by Mileage Master'!$A$1:$B$85,'[BN MASTER LIST.xls]BN Rates
by Mileage Master'!$C$1:$C$85)),'[BN MASTER LIST.xls]BN MN RATES'!$D$19)

 

[donnaK]

Please be very clear in what you tell me to do as I am just learning. Also,
we are waiting with baited breath for your words of wisdom and checking this
site every 5 minutes