Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 9th 05, 09:37 AM posted to microsoft.public.excel.worksheet.functions
zvi
 
Posts: n/a
Default combining conditional formatting and functions

Hi all,

I want an empty given cell, say AA5, to become the color red when a
range of 8 cells in the same row starting 13 columns to the left of AA5
(i.e. the range is N5:U5) is blank. (To explain, if a payment was not
made over the 8 recent months, I want to get the automatic red
"alarm".)

This probably involves:
- conditional formatting of AA5 (to get the red color)
- the IF function (IF the range is blank)
- the OFFSET function (OFFSET(AA5, 0,-13,1,8)

Each month I insert a new column for the new month, making the given
cell AA5 become AB5, but the range (8 cells in the same row starting 13
columns to the left) stays the same. That is why I think the OFFSET
function should be used.

I cannot manage to put it all together and make it work. Any help
appreciated.

Thanks.

Zvi


  #2   Report Post  
Old December 9th 05, 10:46 AM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default combining conditional formatting and functions

Use conditional formatting with that formula

=COUNTA(OFFSET($AA$5,0,-13,1,8))=0

When in C F, change Condition 1 to Formula Is

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"zvi" wrote in message
oups.com...
Hi all,

I want an empty given cell, say AA5, to become the color red when a
range of 8 cells in the same row starting 13 columns to the left of AA5
(i.e. the range is N5:U5) is blank. (To explain, if a payment was not
made over the 8 recent months, I want to get the automatic red
"alarm".)

This probably involves:
- conditional formatting of AA5 (to get the red color)
- the IF function (IF the range is blank)
- the OFFSET function (OFFSET(AA5, 0,-13,1,8)

Each month I insert a new column for the new month, making the given
cell AA5 become AB5, but the range (8 cells in the same row starting 13
columns to the left) stays the same. That is why I think the OFFSET
function should be used.

I cannot manage to put it all together and make it work. Any help
appreciated.

Thanks.

Zvi



  #3   Report Post  
Old December 9th 05, 01:37 PM posted to microsoft.public.excel.worksheet.functions
zvi
 
Posts: n/a
Default combining conditional formatting and functions

Bob,

Your solution looks good.

Thanks,

Zvi



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
cannot use ISEVEN or ISODD functions in Conditional Formatting Scott Paine Excel Worksheet Functions 6 December 6th 05 10:44 PM
Nested functions in conditional formatting formulae Joseph Excel Discussion (Misc queries) 3 October 20th 05 11:52 PM
Multiple FIND functions in Conditional Formatting RocketFuMaster Excel Worksheet Functions 2 March 2nd 05 07:09 PM
Adding more than three Conditions to 'Conditional Formatting' David McRitchie Excel Discussion (Misc queries) 1 November 27th 04 07:03 PM
Conditional Formatting hgrove Excel Worksheet Functions 3 November 9th 04 08:04 AM


All times are GMT +1. The time now is 07:40 PM.

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