+ Reply to Thread
Results 1 to 19 of 19

using the or statement

  1. #1
    Registered User
    Join Date
    10-17-2019
    Location
    Malta, Pembroke
    MS-Off Ver
    2016
    Posts
    11

    using the or statement

    I have the below formula but for some reason it's giving me an error and I can't understand why.

    Just to give you an idea of what I'm trying to do, cell J23 is a drop down list that refers to a table on the same sheet that I'm working on, that can either be 'Victory', 'defeat' or empty. What I want is that if J23 is either Victory or defeat, the cell with formula would remain empty but if it is empty, I want the formula to check if K23 is empty or not. If K23 is empty the cell with the formula would remain empty, but if there is a name in cell K23, I want that name in K23 to be displayed in the cell with the formula. Can someone please explain why I have an error? Thanks in advance

    =IF(OR($J23="Victory",$J23="Defeat"),"",IF($K23="","",$K23))
    Last edited by nutty79; 10-17-2019 at 11:59 AM.

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: using the or statement

    What do you mean by error? Result different than expected or error (#N/A etc.)

    Quote Originally Posted by nutty79 View Post
    if there is Victory or defeat, I want the formula to check if K23 is empty or not.
    Your formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    so IMHO works contrary to intend.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: using the or statement

    Hello and welcome to the forum.

    if J23 is either Victory or defeat, the cell with formula would remain empty
    if there is Victory or defeat, I want the formula to check if K23 is empty or not
    Which one do you want?
    If J23 is Victory or defeat, do you want the formula to return blank (first quote above) or do you want it to move onto the next IF function (second quote above)?

  4. #4
    Registered User
    Join Date
    10-17-2019
    Location
    Malta, Pembroke
    MS-Off Ver
    2016
    Posts
    11

    Re: using the or statement

    the cell is being highlighted with a red dotted line

  5. #5
    Registered User
    Join Date
    10-17-2019
    Location
    Malta, Pembroke
    MS-Off Ver
    2016
    Posts
    11

    Re: using the or statement

    sry i made mistake in description but I edited it

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: using the or statement

    Based on your description, you can use this:

    =IF(J23="",K23&"","")

  7. #7
    Registered User
    Join Date
    10-17-2019
    Location
    Malta, Pembroke
    MS-Off Ver
    2016
    Posts
    11

    Re: using the or statement

    if J23= 'Victory' or 'Defeat' do nothing, if empty check K23. If K23 = empty, do nothing but if K23 ='name', result in cell should be 'name'

  8. #8
    Registered User
    Join Date
    10-17-2019
    Location
    Malta, Pembroke
    MS-Off Ver
    2016
    Posts
    11

    Re: using the or statement

    ok thanks, I'll try it

  9. #9
    Registered User
    Join Date
    10-17-2019
    Location
    Malta, Pembroke
    MS-Off Ver
    2016
    Posts
    11

    Re: using the or statement

    still no luck

  10. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: using the or statement

    I recommend uploading a small representative sample of your data along with the desired results (which you can enter manually) based on that data.

    To upload an Excel workbook, follow these steps:
    1) Click on "Go Advanced"
    2) Click on "Manage Attachments"
    3) Click on "Choose File"
    4) Choose your file and click on "Open"
    5) Click on "Upload"
    6) Click on "Close this window"

  11. #11
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: using the or statement

    Quote Originally Posted by nutty79 View Post
    if j23= 'victory' or 'defeat' do nothing, if empty check k23. If k23 = empty, do nothing but if k23 ='name', result in cell should be 'name'
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  12. #12
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: using the or statement

    Quote Originally Posted by KOKOSEK View Post
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Already suggested in post #6 except $k23 will return 0 here if it's blank.

    Which is why I added the &"" at the end.

    I suspect the OP is not explaining what they want accurately. We'll see when they upload a sample.

  13. #13
    Registered User
    Join Date
    10-17-2019
    Location
    Malta, Pembroke
    MS-Off Ver
    2016
    Posts
    11

    Re: using the or statement

    not sure if I managed to upload the file

  14. #14
    Registered User
    Join Date
    10-17-2019
    Location
    Malta, Pembroke
    MS-Off Ver
    2016
    Posts
    11

    Re: using the or statement

    you received the file? I followed the steps instructed. If yes the cell with the formula in question is at cell D100

  15. #15
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: using the or statement

    No file was uploaded.

  16. #16
    Registered User
    Join Date
    10-17-2019
    Location
    Malta, Pembroke
    MS-Off Ver
    2016
    Posts
    11

    Re: using the or statement

    ok thanks, problem solved with this formula =IF($J23="";IF($K23="";"";$K23);""). Thanks all cause if I was not put in the right direction by both of you, I would not have solved it easily. Thanks again.

    One thing I can't understand, why i hv to use a semicolon instead of just a comma

  17. #17
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: using the or statement

    That should do the same as the formula from post #6.

    All that you have to do is change , to ;
    If you are dragging the formula to the left or right, you would have to include the $ in there too.

    Regional settings depict whether you have to use , or ;

    Nonetheless, I'm glad that it is now solved for you.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  18. #18
    Registered User
    Join Date
    10-17-2019
    Location
    Malta, Pembroke
    MS-Off Ver
    2016
    Posts
    11

    Re: using the or statement

    I can't understand why the formula you suggested didn't work then cause i copied it, pasted it in my sheet and it didn't work, perhaps i left something out, but thanks a lot.

  19. #19
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: using the or statement

    All that you have to do is change , to ;

+ 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: 2
    Last Post: 07-09-2015, 04:25 PM
  2. [SOLVED] If statement to select data - nested statement - assistance
    By petitesouris in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-29-2015, 09:55 PM
  3. compile error expected line number statement end statement
    By mattress58 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-18-2014, 10:12 AM
  4. VBA Compile Error : line number or label or statement or end of statement
    By excellearner121 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-04-2013, 06:41 PM
  5. Replies: 4
    Last Post: 06-01-2012, 10:05 AM
  6. Replies: 4
    Last Post: 05-16-2012, 05:33 PM
  7. [SOLVED] Utilize a Select Case Statement in Target Intersect Statement
    By max57 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-29-2009, 08:55 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