formulas not working or is the sheet not functioning?

**grimlock** · 05-18-2011, 11:40 AM

Hi

can someone please have a look at this sheet.
We thought there were some issues with the sheet and formulas on it not working.

e.g.

i put in some conditional formatting to colour cells R5,6,7 green when R6 & R7 showed their complete statuses, but it doesnt seem to be working

then on the 2nd tab - the formula we placed in cell E3, wouldn't replicate when dragged down,and be relevant to the row it was on, it would just copy the formula.

can anyone help advise?

**Domski** · 05-18-2011, 11:52 AM

Hi,

I can't see any conditional formatting in R5/6/7 on the first sheet and the formula in E3 on the second doesn't reference any rows.

Dom

**NBVC** · 05-18-2011, 11:53 AM

I don't see conditional formatting in those cells at all in the first tab.

Replace formula in E3 of second tab with:

=COUNTIF(INDEX('May Fliped'!$B:$AF,0,ROWS($A$1:$A1)),"complete")

copied down

**CRIMEDOG** · 05-18-2011, 11:55 AM

The conditional Format for the Header Row

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

There isn't any other Conditional Formating on the sheet.
Not sure what you mean by "dragged down"
If there were row numbers in the Formula- they would adjust. But you just have columns:

Please Login or Register  to view this content.

The formulas seem to be working.

**CRIMEDOG** · 05-18-2011, 12:04 PM

For Sheet May Fliped, Highlight all the cells (top right)
GO to Conditional formating & format with formula.
the formula: =A1="Complete"
Set the formatting the way you wish.
This will highlight all cells with "Complete"

**grimlock** · 05-18-2011, 12:07 PM

Hmm, im sure i put conditional formatting in. I've tried again, and attached the sheet again.

Im basically looking to format the cells so that when i get a Complete combined with a Completesd SOW Passed, it colours those cells green along with the customer cell above

**NBVC** · 05-18-2011, 12:09 PM

You have too many double quotes in the formulas....

Try changing to:

=AND($R$6="Complete",$R$7="Completed SOW Passed")

the second question, I answered in previous post....

**CRIMEDOG** · 05-18-2011, 12:19 PM

The problem is: you are surrounding your formula with quotes:

Please Login or Register  to view this content.

It should be:

Please Login or Register  to view this content.

If you highlight the whole sheet and then set the conditional formating as :

Please Login or Register  to view this content.

This will highlight the Company numbers where there is Complete and Completed SOW Passed.
(Do not use $A$2 - so it will be relative)

**grimlock** · 05-18-2011, 06:51 PM

Originally Posted by CRIMEDOG

The problem is: you are surrounding your formula with quotes:

Please Login or Register  to view this content.

It should be:

Please Login or Register  to view this content.

If you highlight the whole sheet and then set the conditional formating as :

Please Login or Register  to view this content.

This will highlight the Company numbers where there is Complete and Completed SOW Passed.
(Do not use $A$2 - so it will be relative)

This is great!, ive used this, but how can i set this formula to highlight all three cells: customer, Action, did you Meet. I've conditionall formatted the sheet as per your instructions and now only the customer cell will highlight