Conditional Formatting - 2 Worksheets one Named Range

[Sam via OfficeKB.com]

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

 

[Bob Phillips]

Try indirecting into the cell, something like

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

--
HTH

Bob Phillips

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

 

[Max]

Another play, using your named range YR2006

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

 

[Sam via OfficeKB.com]

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

 

[Sam via OfficeKB.com]

Hi Max,

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

Cheers,
Sam

 

[Max]

Good to hear that, Sam.
You're welcome !