# Microsoft Office Application Help - Excel Help forum > For Other Platforms(Mac, Google Docs, Mobile OS etc) >  > [SOLVED] Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

## Peaceful Jim

Currently using Google Spreadsheets. I have 2 sheets - "Capping" and "Join Dates" I want cell B2 on Capping to turn red if cell A2 on Join Dates doesn't have the same numerical value. Both cell B2 and A2 have formulas within them that count cells with text in the given range.

On cell B2 of Capping I tried using the "Conditional Formatting" formula "Is Not Equal To" and then referenced the cell A2 on Join Dates. Even though the 2 cells have the same numerical value, cell B2 on Capping turned red. Is this happening because the 'Is Not Equal To" formula is targeting the formula within the referenced cell, rather than its numerical value?

Kind regards, 

Jimmy.

----------


## FDibbins

1st, check to make sure both values ARE identical, even a decimal point 10 positions away can make a difference,
Do a quick test with =1st-cell=2nd-cell and see if it gives TRUE

----------


## Peaceful Jim

The values are indeed identical. The range that the formula addresses does not allow for decimal values.

----------


## Peaceful Jim

Just to add clarity, this is the formula in both cells on the 2 different sheets. 

=COUNTIF(B3:D199,"*")
and
=COUNTIF(A3:A,"*")

Both result in the number 105 being displayed in each cell.

----------


## FDibbins

> The values are indeed identical. The range that the formula addresses does not allow for decimal values.



1.  did you test with a formula to see if they match?  Just because they "looks" the same, doesnt mean the underlying values are actually he same
2.  I dont use google docs, but unless you are using DV or some code, I dont see how you can set a restriction not allowing decimals?

----------


## Peaceful Jim

Admittedly I did not test it with a formula. My logic was that a formula counting cells with text could not count anything but a cell with text, or a cell without, thus there would be no possibility of having a number with a decimal. Then again, logic doesn't always prevail when working with computers... :Roll Eyes (Sarcastic): 

I'm not experienced when it comes to formulas, and adding formulas that reference other sheets just makes it even more difficult for me, but would the formula be =B2=Join Dates!A2 ?

Sorry for my ineptitude!  :Smilie:

----------


## FDibbins

You are not necessarily correct that a formula to count text, will count only text.  =count() will only count numeric, but =countA() will count both numeric and text (at the same time)
Using formulas across sheets (or across other files, for that matter) is really not that scarey.  In fact, it is the exact same as if you were creating a formula within the same sheet, the ONLY real different being that you would include the sheet name (or file name and sheet name) into the formula - and in pretty much all cases, if you use the mouse to point to the cell/s you need, excel (or google docs) will add those for you anyway.

So, yes, if 1 cell is B2 (on "this" sheet), and the other cell is A2 on sheet Join Dates, then yes, the formula would be as you described  :Smilie:

----------


## Peaceful Jim

Thanks again for your assistance. I am getting the following error when using that formula: Error - Formula parse error.  :Confused:

----------


## FDibbins

Like I said, I dont use google docs, so not familiar with formula syntax there  :Frown: 

What formula (exactly) did you use?

----------


## Pete_UK

In Excel you can't explicitly refer to another sheet in a conditional formatting formula. The way round this is to use named ranges. This might be a similar restriction on Google Sheets.

Hope this helps.

Pete

----------


## Peaceful Jim

@FDibbins I used =B2=Join Dates!A2

@Pete_UK Could you please carify what you mean to use named ranges? I'm not at all familiar with excel lingo.

----------


## Pete_UK

It would help if you attached a sample Excel workbook, then we could set this up for you.

To do this, click on *Go Advanced* (below the Edit Window) while you are composing a reply, then scroll down to and click on *Manage Attachments* and the Upload window will open. Click on *Browse* and navigate to (and double-click) the file icon that you want to attach, then click on *Upload* and then on *Close this Window* to return to the Edit window. When you have finished composing your post, click on *Submit Post*.

Please note that the Paperclip icon (Attachments button) does not work on this forum, so don't try to use that.

Hope this helps.

Pete

----------


## Peaceful Jim

I made some progress. Please see this test sheet that I made. https://docs.google.com/spreadsheets...it?usp=sharing

Turns out to reference another sheet that has two words in the name, you have to quote it 'like so'. So I got the =A1='Join Dates'!A1 formula to result in TRUE.

My question is how to I turn A1 of Capping RED if A1 of Join Dates does not equal the same value?

----------


## FDibbins

> In Excel you can't explicitly refer to another sheet in a conditional formatting formula. The way round this is to use named ranges. This might be a similar restriction on Google Sheets.
> 
> Hope this helps.
> 
> Pete



That was a restriction in 2007, Pete, it was removed on later versions.  You can now reference other sheets directly

----------


## FDibbins

I have just tried to play around with that file to apply some CF, and it doesnt seem to want to listen to me  :Smilie:

----------


## xlnitwit

You can't directly refer to another sheet in CF on Google Sheets. You can set the formatting up as cell value is not equal to and then use
=INDIRECT("'Join Dates'!A1")
as the value.

----------


## Peaceful Jim

Thank you, xlnitwit, that has given me exactly what I need! Thank you, everyone, for your help!

----------

