Conditional Formatting Formula Help Part 2

[RalphSE]

Hi,

I am working on a conditional formatting formula that will refer to a different worksheet that the one it is in, I hope this can be done, so here's how the formula needs to be:

in cell X2 of worksheet "10 DAY AVERAGES" I need a formula that tests cell W2 in worksheet "PASTE DATA" to see if it's greater than the 20 day average of cells W2 through D2 in worksheet "PASTE DATA", and if so I will program a certain highlighting color via conditional formatting, I would appreciate very much some help with this formula

THANKS!

[JE McGimpsey]

One way:

Name 'PASTE DATA'!W2, say, TheTest
Name 'PASTE DATA'!D2:W2, say, TheRange

In '10 DAY AVERAGES'!X2, choose Data/Conditional Formatting, and set up
the dropdowns and textboxes to read

CF1: Formula is = TheTest > AVERAGE(TheRange)
Format1: <patterns>/<color>

In article <[email protected]>,
RalphSE <[email protected]> wrote:

> Hi,
>
> I am working on a conditional formatting formula that will refer to a
> different worksheet that the one it is in, I hope this can be done, so
> here's how the formula needs to be:
>
> in cell X2 of worksheet "10 DAY AVERAGES" I need a formula that tests
> cell W2 in worksheet "PASTE DATA" to see if it's greater than the 20
> day average of cells W2 through D2 in worksheet "PASTE DATA", and if so
> I will program a certain highlighting color via conditional formatting,
> I would appreciate very much some help with this formula
>
> THANKS!

[RalphSE]

bummer, i tried that but it gave me an error message saying you cannot reference other worksheets in conditional formatting

[JE McGimpsey]

Then you didn't name the ranges and use the names instead of the range
references...

In article <[email protected]>,
RalphSE <[email protected]> wrote:

> bummer, i tried that but it gave me an error message saying you cannot
> reference other worksheets in conditional formatting

[RalphSE]

not sure what you mean JE, can you try to explain it a little more clearly please?

[RalphSE]

will this method work if i copy the conditional formatting to other cells, i.e., will the test cell and range cells change accordingly?

[JE McGimpsey]

You can only reference other sheets in Conditional Formatting by using
named ranges.

Select your range, then type a name in the name box at the left of the
formula bar. Hit enter. You may now use that name as a substitute for
the range reference in a formula.

In article <[email protected]>,
RalphSE <[email protected]> wrote:

> not sure what you mean JE, can you try to explain it a little more
> clearly please?

[JE McGimpsey]

When you directly name a range, the reference won't change.

However, you could use dynamic ranges instead:

http://cpearson.com/excel/named.htm#Dynamic


In article <[email protected]>,
RalphSE <[email protected]> wrote:

> will this method work if i copy the conditional formatting to other
> cells, i.e., will the test cell and range cells change accordingly?

[RalphSE]

thanks for the link JE, this stuff is a little over my head, i still dont get how to name my ranges so they will be dynamic, i'd really appreciate it if you could explain that in terms of the example I've given here PLEASE

[RalphSE]

ok then, does ANYONE know how to make the ranges dynamic so that I can finish this project? I would really appreciate the help, thanks!

[Debra Dalgleish]

There's another description here:

http://www.contextures.com/xlNames01.html

If you're still having trouble, and explain where you're getting stuck,
someone may be able to help.

RalphSE wrote:
> ok then, does ANYONE know how to make the ranges dynamic so that I can
> finish this project? I would really appreciate the help, thanks!
>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html