# Conditional Forumla with Forumla assigned- Help needed

1. ## Conditional Forumla with Forumla assigned- Help needed

Hi All,

Hope your all well? Hopefully this is a clear and easy request.

I have a sports spreadsheet, which changes weekly. The purpose of my post is that i am struggling to format cells accordingly (if it is possible).

I have cell b5, which is a maximum taken over a 52 week period (52 weeks are to the right).
I have cell R5, which is week 15 (where we have picked off on the calendar year)
What id like to do is have cell R5 conditionaly format based on a couple of rules, this would be taken again cell b5 (the max).

I believe this would need to be done by a forumla as the max number can obviously change if it is beaten during the weekly testing.
The rules would be.

Anything from the max score -3 would be green.
Anything between -3 and -6 of the max score would orange.
Anything -6 and below the max score would be red.

Say player one's maximum is 70, week 1 he scores the 70 (green), week 2 he scores 68 (green), week 3 he scores 60 (red), week 4 he scores 72 (green, but week 2 would change to orange accordingly)

The more i look into it the more i dont believe its possible.

Any help or suggestions would be greatly appreciated.

2. ## Re: Conditional Forumla with Forumla assigned- Help needed

Hi,

In B5, put the following formula:
Formula:
`Please Login or Register  to view this content.`
This will cause the maximum to change dynamically. Then highlight cells C5:BB5 and down as many rows as you want/need, and go to Conditional Formatting --> Use a formula to determine which cells to format, and enter this formula:
Formula:
`Please Login or Register  to view this content.`
and format it green, then add a new rule with this formula:
Formula:
`Please Login or Register  to view this content.`
and format it orange, then add a third rule with this formula:
Formula:
`Please Login or Register  to view this content.`
then format it red.

Then when the maximum changes, so does the formatting.

3. ## Re: Conditional Forumla with Forumla assigned- Help needed

Absolute genius! Just applied all that and works fantastically, didnt think it would actually be possible given the exact criteria. Aboslutely thrilled. Will really help the work i am doing. Many many thanks.

4. ## Re: Conditional Forumla with Forumla assigned- Help needed

You're welcome! Happy to help

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1