Conditional Formatting - 2 Worksheets one Named Range
 

Hi All,

I have two worksheets using numeric values:

Sheet1 Range N17:N100 (84 Rows)
Sheet1 Range K17:K100 (84 Rows) - If condition is met apply Strikethrough
Conditional Format
Sheet2 Range K197:K280 (84 Rows) - Named Range "YR2006"

I would like a Conditional Formula to set criteria below and apply CF:
IF a cell value in Sheet1 N17:N100 is 0 (greater than zero) AND = (greater
than or equal to) its corresponding cell value in Sheet2 Named Range "YR2006".


The cells in Sheet1 Range N17:N100 should be compared individually to their
corresponding cell in Sheet2; ie. Sheet1 N17 is compared to Sheet2 K197,
Sheet1 N18 is compared to Sheet2 K198 and so on.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1

Conditional Formatting - 2 Worksheets one Named Range
 

Try indirecting into the cell, something like

=N17=INDIRECT("Sheet2!"&T(ADDRESS(ROW(N17)+180,COL UMN(N17)-3)))

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:6ce8bb4ed6c67@uwe...
Hi All,

I have two worksheets using numeric values:

Sheet1 Range N17:N100 (84 Rows)
Sheet1 Range K17:K100 (84 Rows) - If condition is met apply Strikethrough
Conditional Format
Sheet2 Range K197:K280 (84 Rows) - Named Range "YR2006"

I would like a Conditional Formula to set criteria below and apply CF:
IF a cell value in Sheet1 N17:N100 is 0 (greater than zero) AND =
(greater
than or equal to) its corresponding cell value in Sheet2 Named Range
"YR2006".


The cells in Sheet1 Range N17:N100 should be compared individually to
their
corresponding cell in Sheet2; ie. Sheet1 N17 is compared to Sheet2 K197,
Sheet1 N18 is compared to Sheet2 K198 and so on.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1

Conditional Formatting - 2 Worksheets one Named Range
 

Another play, using your named range YR2006

Select K17:K100 in Sheet1,
then apply the CF using the formula:
=AND(N170,N17=INDEX(YR2006,ROW(A1)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:6ce8bb4ed6c67@uwe...
Hi All,

I have two worksheets using numeric values:

Sheet1 Range N17:N100 (84 Rows)
Sheet1 Range K17:K100 (84 Rows) - If condition is met apply Strikethrough
Conditional Format
Sheet2 Range K197:K280 (84 Rows) - Named Range "YR2006"

I would like a Conditional Formula to set criteria below and apply CF:
IF a cell value in Sheet1 N17:N100 is 0 (greater than zero) AND =
(greater
than or equal to) its corresponding cell value in Sheet2 Named Range
"YR2006".


The cells in Sheet1 Range N17:N100 should be compared individually to
their
corresponding cell in Sheet2; ie. Sheet1 N17 is compared to Sheet2 K197,
Sheet1 N18 is compared to Sheet2 K198 and so on.

Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1

Conditional Formatting - 2 Worksheets one Named Range
 

Hi Bob,

Thank you for your time and assistance. I've played about with the Formula
but unfortunately, I can't get it to work.

Cheers,
Sam

Bob Phillips wrote:
Try indirecting into the cell, something like

=N17=INDIRECT("Sheet2!"&T(ADDRESS(ROW(N17)+180,CO LUMN(N17)-3)))

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1

Conditional Formatting - 2 Worksheets one Named Range
 

Hi Max,

Thank you very much for your time and assistance. The Formula works Great!

Cheers,
Sam

Max wrote:
Another play, using your named range YR2006

Select K17:K100 in Sheet1,
then apply the CF using the formula:
=AND(N170,N17=INDEX(YR2006,ROW(A1)))

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1

Conditional Formatting - 2 Worksheets one Named Range
 

Good to hear that, Sam.
You're welcome !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:6ce9b5ccbfe3a@uwe...
Hi Max,

Thank you very much for your time and assistance. The Formula works Great!

Cheers,
Sam