+ Reply to Thread
Results 1 to 8 of 8

Adjacent cells omitted warning, why?

  1. #1
    Registered User
    Join Date
    09-11-2013
    Location
    Virginia Beach, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Adjacent cells omitted warning, why?

    I’m getting the “Formula Omits Adjacent Cells” warning for the following formula on some cells but not others; however everything is working as it should. Why is excel complaining about omitted adjacent cells? It all looks good to me.


    =IF(COUNTBLANK(C$1:E$1)=3,0,IF((C$1*C10=1)+(E$1*E10=1),0,1))

    I have attached the file I am working on with the hidden rows unhidden so you can see whats going on. It is a weekly football picks spreadsheet that allows you to mark a 1 above the winning teams to get the number of incorrect picks in the left column. If no 1 marks the winning team then the above formula should count the blanks total for that team as 3 and simply assign a 0 incorrect picks for that game. If there is a 1, then the main if formula will tabulate a correct or incorrect pick accordingly.
    Attached Files Attached Files
    Last edited by AliGW; 11-18-2019 at 01:58 PM. Reason: Solved tag added - no need to edit thread title or add solved to post. Thanks.

  2. #2
    Registered User
    Join Date
    06-27-2013
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Adjacent cells omitted warning, why?

    if you click on the error and select update formula to include cells, it will get rid of the error while keeping the correct value.
    You can also fix the first row and then drag down and it will clear the error mark out of the cells.
    Last edited by liz5818; 09-11-2013 at 12:31 PM.

  3. #3
    Registered User
    Join Date
    09-11-2013
    Location
    Virginia Beach, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Adjacent cells omitted warning, why?

    That won't work because then it tries to add another cell in the calculation that I don't wan't. My formula is exactly what I want to do but excel doesn't like it for some buggy reason.
    Last edited by simusphere; 09-11-2013 at 01:29 PM.

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Adjacent cells omitted warning, why?

    Go to File, Options, Formulas - In the Error Checking rules remove check from "Formulas which omits cells in a region"
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Registered User
    Join Date
    09-11-2013
    Location
    Virginia Beach, USA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Adjacent cells omitted warning, why?

    That did the trick AlKey, I'll mark this as fixed.

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: [SOLVED] Adjacent cells omitted warning, why?

    You're Welcome. Don't forget to thank those who helped by clicking on Add Reputation *

  7. #7
    Registered User
    Join Date
    05-16-2016
    Location
    Canyon Lake, Ca
    MS-Off Ver
    Various
    Posts
    1

    Re: Adjacent cells omitted warning, why?

    Thank you so much for your answer. I have been living with this annoyance for more years than I want to admit to :/) Like many things in life, we put up with small things that aren't so big to take the time to fix, but once we do we are overjoyed at the difference. I never thought that there would be anything that could prevent the problem you addressed and today for some reason, I looked it up and got your answer from 3 years ago, and you made my whole day ! ! ! For me I had to go to Tools to get to Options instead of File, Options, but that may be because I'm using a different or older version. Only wanted to point that out in case someone had a similar version as mine. Thank you so much again. In our busy society people could go about their own business and not be concerned about other people's questions. I have been grateful so many times for those that have cared and answered a question about something I was struggling with, and today I am very grateful for yours.

    Thank you again.

    Bigbird
    Mike

  8. #8
    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: [SOLVED] Adjacent cells omitted warning, why?

    Big Bird, welcome to the forum and thank you for your feedback
    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

  9. #9
    Registered User
    Join Date
    11-18-2019
    Location
    Hartsville, SC
    MS-Off Ver
    2016./2019
    Posts
    1

    Re: [SOLVED] Adjacent cells omitted warning, why?

    I had this problem and realized my formula was using relative referencing (A1) so it was incrementing in each lower cell which was giving me the error in all but the first cell. Once I made the Column static ($A1) and copied it down, the error went away.
    I don't like the idea of shutting off the error checking globally which is, if I understood it correctly, what was suggested.

    Kevin

+ 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. [SOLVED] Concatenating adjacent and non adjacent cells depending on entries
    By TBHusker84 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2013, 10:27 AM
  2. Replies: 0
    Last Post: 07-31-2013, 06:03 AM
  3. Replies: 3
    Last Post: 07-30-2012, 11:20 AM
  4. Adjacent cells in non-adjacent list?
    By anakeimai in forum Excel General
    Replies: 3
    Last Post: 09-10-2008, 05:49 PM
  5. Excel assumes wrong year when year omitted
    By CircleSky in forum Excel General
    Replies: 3
    Last Post: 06-12-2006, 05:20 PM

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