Conditional Format to <sum current cell> + <5 cells above and below>

Hi All,

I need to Conditional Format every cell in my work sheet. No matter which cell I click on...
1. if SUM of 'current cell' + '5 cells above and/or below' is > 60, make the cell red

This might be a circular reference, however, I thought Conditional Formatting would handle it...?

Can someone please advise how I would apply this Conditional Format to every cell?

What I've done so far...
After selecting 'every cell' in the work sheet, I've applied a Conditional Format something like this.

=if(SUM(or(OFFSET(address(row(),Column()),0,0,5,1))>60,(OFFSET(address(row(),Column()),0,0,-5,1))>60,),1,0)

This may or may not be the right way, but in any case it's not working for me...Any help would be greatly appreciated

Nelg

Thanks for the response DILIPandey,

Your suggestion might work...
I haven't used this function before and I'm a little confused as to how you've set the functin up.
I've googled 'defining names' and followed/understand the process, however I don't understand how you've given the instruction to reference the 'current cell'
eg. I go into 'name manager' and I see the formula =OFFSET(Sheet1!H19,-5,0,11,1).
the 'name manager' knows I am in Cell H19. can you advise how you set this up?

Many, many thanks

Nelg

Thanks again DILIPandey,

Sorry for the need to 'dumb' it down for me...

you start a 'new name'
Name: "data"
Scope: "sheet1"
Comment:
Referes to: "=OFFSET(?????,-5,0,11,1)"

How have you gotten the formula to recognise which cell is active?

Thanks again

Nelg



'reference' c

Thanks DILIPandey,

Think I figured it out...

it appears if i use the formula =OFFSET(SHEET1!A1,-5,0,11,1)

'A1' references the 'entire sheet1' and the 'defining name' function knows which is the 'current cell'

I can definately use your suggestion...MANY THANKS!

Nelg

@ GlenNelg

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Hi All,

I'm not sure if I can re-start a thread...my appologies if it's in the wrong list.

The suggestion of using Cell Naming worked a treat, however I'm now trying to make it more complex...

I'd like to SUM the previous 5 rows and/or until their is an empty cell.
eg. if the formula encounters a blankcell 3 rows up, it only sums up until the blankrow
any help would be greatly appreciated

Cheers

Nelg

ok, thanks