+ Reply to Thread
Results 1 to 17 of 17

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

  1. #1
    Registered User
    Join Date
    03-15-2017
    Location
    QLD, Australia
    MS-Off Ver
    365
    Posts
    14

    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.
    Last edited by AliGW; 03-02-2018 at 02:08 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    03-15-2017
    Location
    QLD, Australia
    MS-Off Ver
    365
    Posts
    14

    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. #4
    Registered User
    Join Date
    03-15-2017
    Location
    QLD, Australia
    MS-Off Ver
    365
    Posts
    14

    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. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

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

    Quote Originally Posted by Peaceful Jim View Post
    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. #6
    Registered User
    Join Date
    03-15-2017
    Location
    QLD, Australia
    MS-Off Ver
    365
    Posts
    14

    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. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #8
    Registered User
    Join Date
    03-15-2017
    Location
    QLD, Australia
    MS-Off Ver
    365
    Posts
    14

    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. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,600

    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. #11
    Registered User
    Join Date
    03-15-2017
    Location
    QLD, Australia
    MS-Off Ver
    365
    Posts
    14

    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. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,600

    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. #13
    Registered User
    Join Date
    03-15-2017
    Location
    QLD, Australia
    MS-Off Ver
    365
    Posts
    14

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

    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?

  14. #14
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

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

    Quote Originally Posted by Pete_UK View Post
    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. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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. #16
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    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.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  17. #17
    Registered User
    Join Date
    03-15-2017
    Location
    QLD, Australia
    MS-Off Ver
    365
    Posts
    14

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 03-09-2016, 12:17 PM
  2. [SOLVED] Delete Rows if a cell does not equal "Finished" or "Complete"
    By Justair07 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 09-27-2013, 12:40 PM
  3. [SOLVED] Cell Formula to count time with "Greater Than or Equal to" and "Less Than"
    By chriswhite1982 in forum Excel General
    Replies: 3
    Last Post: 06-16-2013, 12:30 AM
  4. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  5. Replies: 1
    Last Post: 04-01-2013, 03:31 PM
  6. [SOLVED] IF formula with logical " Less than Or Equal To" NOT WORKING (Data is week number)
    By zeko90 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-28-2013, 02:51 PM
  7. "The "sheets" method from the "_Global" object have failed."
    By mankit87 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-08-2011, 08:53 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1