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>

 

[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...

 

[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.

 

[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

 

[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.