Conditional formatting data from another worksheet

[ParaSitius]

Hi all,

I'm currently doing a MOST course and have recently come across conditional formatting for the first time which I thought would be quite useful in some of the work that I do in Excel 2003.

I've done a spot of searching on this forum before posting this question as I have come a bit stuck with getting CF to work correctly. I'm trying to use two colours depending on whether the cell is blank or is one or higher, a cell with zero is left white. I would like a blank cell to be purple and the cell >=1 to be yellow.

I believe I have got the initial part of pulling data from worksheet 'Grid' to worksheet 'T.I. Count' correct by using the following formula (the cell DZ3 is being used at it is the latest data in a large sheet).

=IF(ISBLANK(Grid!DZ3),"",Grid!DZ3) --the formula is used all the way down to row DZ81.

The above formula gives me the correct result in 'T.I. Count' so I can see the blanks, zeros and other numbers but I'm stuck with getting CF to show the correct colour.

I've tried using =MATCH but the examples I've tried to follow don't seem to work for me and all I seem to get is a white cell.

Any help would be appreciated.

 

[shyy]

The formula shouldn't it be

=IF(ISBLANK(Grid!DZ3),"",Grid!DZ3) should be =IF(ISBLANK(Grid!DZ3),TRUE,1,0) apply the color if it is blank.


ISBLANK returns either TRUE or FALSE, so to say if its blank will not work.

 

[shyy]

Your IF formula had wrong syntax, it is not like a SUMIF(range,lookupvalue,sumrange) so my formula on top isn;t working use this:

=IF(ISBLANK(Grid!DZ3)=TRUE,1,0)

 

[ParaSitius]

Hi Shyy,

I gave your first formula a try and Excel tells me their are too many argument's.

Your second formula gives me a result of 0 when there is a cell with a number in it or 1 for a blank cell.

The formula I've used gives me the result I'm looking for which matches the blank, 0 or number that is found in the Grid worksheet, my only problem is getting CF to colour in the cell.

 

[shyy]

First formula returned error of too many arguments because I simply copied and edit your formula which already had the wrong syntax to it.

Second formula is correct, if the cell is blank to return 1 (true)

reading your post again try this:

Create three new rules for each cell.

=IF(Grid!DZ3=0,1,0) Give white color
=IF(ISBLANK(Grid!DZ3)=TRUE,1,0) or use =IF(Grid!DZ3="",1,0) Give purple color
=IF(Grid!DZ3>=1,1,0) Give Yellow color

Color is not display but I used the following formulas.

=IF(A1="",1,0)
=IF(A1>=1,1,0)
=IF(A1=0,1,0)

Excel 2010

	A	B
Returns White
Returns Yellow
Returns Purple
Returns Yellow

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2[/TD]

</tbody>

Sheet6​

Hi Shyy,

I gave your first formula a try and Excel tells me their are too many argument's.

Your second formula gives me a result of 0 when there is a cell with a number in it or 1 for a blank cell.

The formula I've used gives me the result I'm looking for which matches the blank, 0 or number that is found in the Grid worksheet, my only problem is getting CF to colour in the cell.

 

[ParaSitius]

I finally figured out what I did wrong in the CF and it was all down to the my stupidity and having a space inbetween the double quotes.

All I needed were two conditions that say the following

cell value is equal to ="" -- colour purple
cell value is greater then or equal to 1 -- colour yellow

The answer was staring me in the face every time I looked at the cell formula.

Shyy, my apologies if I have wasted your time in helping me with this, it was much appreciated that you took the effort in helping me out.

 

[shyy]

haha happens to us all, thanks for the feeedback.