Conditional Formatting

[amygirl]

Hi
I am using a formula to bring in data from another sheet in the same book. WHen there is an #N/A I want the conditional formatting to gray it out. The problem is that tho the cell shows #N/A the conditional formatting doesn't see it as #N/A...it sees the code:

=INDEX(Duration!$I:$I,MATCH(1,(Duration!$C:$C=$A2)*(Duration!$D:$D=$A28)*(Duration!$G:$G=K$7),0))

I tried using indirect in the conditional formatting but it was too confusing and I blacked out. When I came to, I was in the fetal postition whimpering.

If you need more information, please let me know.

Thanks!

 

[T. Valko]

Hi
I am using a formula to bring in data from another sheet in the same book. WHen there is an #N/A I want the conditional formatting to gray it out. The problem is that tho the cell shows #N/A the conditional formatting doesn't see it as #N/A...it sees the code:

=INDEX(Duration!$I:$I,MATCH(1,(Duration!$C:$C=$A2)*(Duration!$D:$D=$A28)*(Duration!$G:$G=K$7),0))

I tried using indirect in the conditional formatting but it was too confusing and I blacked out. When I came to, I was in the fetal postition whimpering.

If you need more information, please let me know.

Thanks!

What version of Excel are you using?

What cell is this formula in?

 

[amygirl]

Excel 2007

The formula is in cell K12

 

[T. Valko]

Excel 2007

The formula is in cell K12

OK, try this...

• Select cell K12
• Goto the Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a formula to determine which cells to format
• Enter this formula in the box below:
  =ISNA(K12)
• Click the Format button
• Select the desired style(s)
• OK out

 

[amygirl]

Thanks that worked!

 

[T. Valko]

Thanks that worked!

You're welcome. Thanks for the feedback!