+ Reply to Thread
Results 1 to 13 of 13

IF function

  1. #1
    Registered User
    Join Date
    08-21-2011
    Location
    Boynton Beach, FL
    MS-Off Ver
    Excel 2010
    Posts
    13

    IF function

    I have a table with several columns and want to make it work this way
    If E7 is empty, than H7is empty
    If E7 has X, than H7 is empty
    If E7 has any other value than X H7 has X

    =IF(E7<>0,"X",IF(E7="","",IF(E7=X,"")))

    My formula isn't working, because X falls under <>0, so when there is an X in E7 I get an X in H7, but I need it to be available to accept a different value.
    Also, how can I apply this formula to several columns within the same table, meaning if there is a value in, let's say, E7 other cells (like J7, K7) will automaticaly receive an X?
    In this case E7 is the first cell from the left, so all others in the row will be filled in as I go, but what if value has to be put in in cell K7? I can go directly to it, but wil it put an X into other cells.
    Basicaly all these cells receive the same value but only one of them in given row can have it, the others I want to have an X, so I don't put anything in it by accident.
    Thank You in advance for helping

  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,933

    Re: IF function

    =if(or(E7="",E7="X"),"X")
    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
    08-21-2011
    Location
    Boynton Beach, FL
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: IF function

    Thanks, but when I pu your formula in H7 all cells in the column show an X, if i put X in E7 nothing changes in H7, if I put anything different than X in E7, H7 shows FALSE, I want it to be empty.
    Can you suggest something else?
    Thanks

  4. #4
    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,933

    Re: IF function

    oops my bad, sorry, i left out part of the formula

    =IF(OR(E7="",E7="X"),"","X")

  5. #5
    Registered User
    Join Date
    08-21-2011
    Location
    Boynton Beach, FL
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: IF function

    Thanks, it works, but when I put the amount into H7 it shows inconsistent calculated column formula, I noticed that with my formulas as well (althogh calculations are correct) shoul I ignore it or there's a fix for this?
    I also wanted to be able to put an amount in F7 if E7 has a value other than X or blank.
    I used =IF(E7="X","X",IF(E7="","",IF(E7<>0,0)))
    and it works, but it puts $0.00 and when I use real amount the I get same "inconsistent" message. I just don't know how to do it.
    It won't let me put <>0 again at the end.
    Thanks

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: IF function

    How about an actual workbook sample as to what you have and what you desire?
    HTH
    Regards, Jeff

  7. #7
    Registered User
    Join Date
    08-21-2011
    Location
    Boynton Beach, FL
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: IF function

    OK, how do I get it to you?

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: IF function

    Attach a sample workbook.

    When you are in Advanced reply, click on the paperclip to open the upload window.

    View Pic

  9. #9
    Registered User
    Join Date
    08-21-2011
    Location
    Boynton Beach, FL
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: IF function

    Thank You for helping
    Attached Files Attached Files

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: IF function

    I don't see any references to what you want in F7 if E7 has what...

    So,

    If E7 = "X" then you want "X" in F7
    If E7 = nothing then you want nothing in F7
    If E7 = what then you want what

  11. #11
    Registered User
    Join Date
    08-21-2011
    Location
    Boynton Beach, FL
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: IF function

    If E7 = "X" then you want "X" in F7
    If E7 = nothing then you want nothing in F7
    if E7 = number or word I need to put an amount into F7 and the way it is now $0.00 comes up. When I change it to real number it shows inconsistent column formula.
    And if anything is in that or other formula controlled cell, formula disapeares, I have to copy it from untouched cell of the same column - is there any way to protect formula in cells?

  12. #12
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: IF function

    Quote Originally Posted by alexu95 View Post
    if E7 = number or word I need to put an amount into F7
    You can't directly enter an amount into F7 and at the same time have a formula. It's one or the other...

    What does this do for you? >> =IF(E7="X","X",IF(ISBLANK(E7),"","A Number"))

    Quote Originally Posted by alexu95 View Post
    is there any way to protect formula in cells?
    If you want to protect a formula, you have to protect the sheet.

    Review tab >> Changes >> Protect Sheet

  13. #13
    Registered User
    Join Date
    08-21-2011
    Location
    Boynton Beach, FL
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: IF function

    Thanks, Jeff. It works similar to other formulas and has that little column formula inconsistency sign. Should I simply ignore it or it's possible to turn this feature of for this worksheet?
    Can you please look at the first message in this thread about filling in cells in the one given row. I'd like to use it on second worksheet in this workbook and other similar workbooks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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