# Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

1. ## Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

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.

2. ## Re: Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

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

3. ## Re: Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

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

4. ## Re: Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

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.

5. ## Re: Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

Originally Posted by Peaceful Jim
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?

6. ## Re: Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

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...

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!

7. ## Re: Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

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

8. ## Re: Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

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

9. ## Re: Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

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

What formula (exactly) did you use?

10. ## Re: Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

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

11. ## Re: Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

@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.

12. ## Re: Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

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

13. ## Re: Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

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?

14. ## Re: Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

Originally Posted by 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
That was a restriction in 2007, Pete, it was removed on later versions. You can now reference other sheets directly

15. ## Re: Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

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

16. ## Re: Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

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.

17. ## Re: Google Sheets: Need help with "Is Not Equal To" formula over two sheets.

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

## X vBulletin 4.1.8 Debug Information

• Page Generation 1.54917 seconds
• Memory Usage 9,369KB
• Queries Executed 16 (?)
Template Usage (34):
• (2)bbcode_quote
• (1)footer
• (1)forumrules
• (1)gobutton
• (17)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (17)postbit_legacy
• (17)postbit_onlinestatus
• (17)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./includes/functions_notice.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php

Hooks Called (48):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_display_complete
• memberaction_dropdown
• tag_fetchbit_complete