Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting on cell with a formula
Hi! I'm having trouble with conditional formatting on cells in one sheet that
have formulas referring to another sheet. I have one sheet called "Worksheet". The user enters data in that sheet that is then transferred to another sheet called "Print Sheet". Say Print Sheet cell A1 has the formula "=IF(Worksheet!A60,Worksheet!A6,"")" The conditional formatting I want on Print Sheet is if Print Sheet cell A1 has the value from Worksheet A6 showing (in otherwords Worksheet A6 was 0) then Print Sheet cell A1 is to be colored gray. If the IF statement proved false and Print Sheet A1 shows blank or "", then Print Sheet A1 is to be conditionally formatted nothing. I tried the conditional formatting "cell value is" <0 and it still conditionally formatted the cell fill in gray because Print Sheet cell A1 does have a cell value... the IF statement formula... see what I mean? I also tried reference to sheet Worksheet cell A6, but Conditional Formatting doesn't like to look to other sheets. I even tried using this formuly [Staff Schedule.xls]Worksheet!$A$6 to help conditonal formatting deal with reference to another worksheet and it didn't like it. Can anyone help me with this? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting on cell with a formula
Try using conditional formatting with "formula is" option using formula
=A1<"" "Kass" wrote: Hi! I'm having trouble with conditional formatting on cells in one sheet that have formulas referring to another sheet. I have one sheet called "Worksheet". The user enters data in that sheet that is then transferred to another sheet called "Print Sheet". Say Print Sheet cell A1 has the formula "=IF(Worksheet!A60,Worksheet!A6,"")" The conditional formatting I want on Print Sheet is if Print Sheet cell A1 has the value from Worksheet A6 showing (in otherwords Worksheet A6 was 0) then Print Sheet cell A1 is to be colored gray. If the IF statement proved false and Print Sheet A1 shows blank or "", then Print Sheet A1 is to be conditionally formatted nothing. I tried the conditional formatting "cell value is" <0 and it still conditionally formatted the cell fill in gray because Print Sheet cell A1 does have a cell value... the IF statement formula... see what I mean? I also tried reference to sheet Worksheet cell A6, but Conditional Formatting doesn't like to look to other sheets. I even tried using this formuly [Staff Schedule.xls]Worksheet!$A$6 to help conditonal formatting deal with reference to another worksheet and it didn't like it. Can anyone help me with this? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting on cell with a formula
Select A1 then FormatCFFormula is: =A1<""
FormatPattern. Gord Dibben MS Excel MVP On Sat, 29 Sep 2007 15:48:00 -0700, Kass wrote: Hi! I'm having trouble with conditional formatting on cells in one sheet that have formulas referring to another sheet. I have one sheet called "Worksheet". The user enters data in that sheet that is then transferred to another sheet called "Print Sheet". Say Print Sheet cell A1 has the formula "=IF(Worksheet!A60,Worksheet!A6,"")" The conditional formatting I want on Print Sheet is if Print Sheet cell A1 has the value from Worksheet A6 showing (in otherwords Worksheet A6 was 0) then Print Sheet cell A1 is to be colored gray. If the IF statement proved false and Print Sheet A1 shows blank or "", then Print Sheet A1 is to be conditionally formatted nothing. I tried the conditional formatting "cell value is" <0 and it still conditionally formatted the cell fill in gray because Print Sheet cell A1 does have a cell value... the IF statement formula... see what I mean? I also tried reference to sheet Worksheet cell A6, but Conditional Formatting doesn't like to look to other sheets. I even tried using this formuly [Staff Schedule.xls]Worksheet!$A$6 to help conditonal formatting deal with reference to another worksheet and it didn't like it. Can anyone help me with this? Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting on cell with a formula
Oh, I just got it.... =NOT($A$11=("")). I also see 2 replys that look less
complicated. Thanks daddylonglegs and Gord Dibben! I always do things the hard way. Thanks so much for your time! "Kass" wrote: Hi! I'm having trouble with conditional formatting on cells in one sheet that have formulas referring to another sheet. I have one sheet called "Worksheet". The user enters data in that sheet that is then transferred to another sheet called "Print Sheet". Say Print Sheet cell A1 has the formula "=IF(Worksheet!A60,Worksheet!A6,"")" The conditional formatting I want on Print Sheet is if Print Sheet cell A1 has the value from Worksheet A6 showing (in otherwords Worksheet A6 was 0) then Print Sheet cell A1 is to be colored gray. If the IF statement proved false and Print Sheet A1 shows blank or "", then Print Sheet A1 is to be conditionally formatted nothing. I tried the conditional formatting "cell value is" <0 and it still conditionally formatted the cell fill in gray because Print Sheet cell A1 does have a cell value... the IF statement formula... see what I mean? I also tried reference to sheet Worksheet cell A6, but Conditional Formatting doesn't like to look to other sheets. I even tried using this formuly [Staff Schedule.xls]Worksheet!$A$6 to help conditonal formatting deal with reference to another worksheet and it didn't like it. Can anyone help me with this? Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting on cell with a formula
I need some similar help - can you possibly help me?
In a cell in a wksheet, it has a formula that pulls the value/words from another worksheet cell. If the cell it's referencing is blank, Excel gives me a "O" in the destination cell. How do I make it give a blank if the referenced cell is empty? "daddylonglegs" wrote: Try using conditional formatting with "formula is" option using formula =A1<"" "Kass" wrote: Hi! I'm having trouble with conditional formatting on cells in one sheet that have formulas referring to another sheet. I have one sheet called "Worksheet". The user enters data in that sheet that is then transferred to another sheet called "Print Sheet". Say Print Sheet cell A1 has the formula "=IF(Worksheet!A60,Worksheet!A6,"")" The conditional formatting I want on Print Sheet is if Print Sheet cell A1 has the value from Worksheet A6 showing (in otherwords Worksheet A6 was 0) then Print Sheet cell A1 is to be colored gray. If the IF statement proved false and Print Sheet A1 shows blank or "", then Print Sheet A1 is to be conditionally formatted nothing. I tried the conditional formatting "cell value is" <0 and it still conditionally formatted the cell fill in gray because Print Sheet cell A1 does have a cell value... the IF statement formula... see what I mean? I also tried reference to sheet Worksheet cell A6, but Conditional Formatting doesn't like to look to other sheets. I even tried using this formuly [Staff Schedule.xls]Worksheet!$A$6 to help conditonal formatting deal with reference to another worksheet and it didn't like it. Can anyone help me with this? Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting on cell with a formula
Instead of using this kind of link formula: =Sheet1!A1
you could try something like this: =IF(Sheet1!A1="","",Sheet1!A1) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Nicole L." wrote: I need some similar help - can you possibly help me? In a cell in a wksheet, it has a formula that pulls the value/words from another worksheet cell. If the cell it's referencing is blank, Excel gives me a "O" in the destination cell. How do I make it give a blank if the referenced cell is empty? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is conditional formatting or formula possible by cell color? | Excel Worksheet Functions | |||
Recognizing Cell Formatting To Use In A Formula Or Conditional For | Excel Worksheet Functions | |||
Conditional formatting formula that uses VLookup, based on content of another cell | Excel Discussion (Misc queries) | |||
Conditional formatting formula that uses VLookup, based on content of another cell | Excel Discussion (Misc queries) | |||
Conditional Formatting if cell content is a formula | Excel Worksheet Functions |