+ Reply to Thread
Results 1 to 16 of 16

Using IFAND Statements

Hybrid View

  1. 10-12-2012, 03:38 PM #1
    hettbarn
    hettbarn is offline
    Registered User
    Join Date
    08-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Using IFAND Statements

    Hello,
    I am trying to do a quick validation which requires a look to see if data is in A1 then data should be in A2 - and if nothing in A1 then nothing should be in A2.

    I know this should be easy - but I am at a loss.

    Any suggestions would be GREATLY appreciated!!!

    Thanks!
    Lisa
    Reply With QuoteRegister To Reply
  2. 10-12-2012, 04:56 PM #2
    Jakobshavn
    Jakobshavn is offline
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Using IFAND Statements

    This formula will perform your test:

    =A1=A2
    Gary's Student
    Reply With QuoteRegister To Reply
  3. 10-12-2012, 05:03 PM #3
    ConneXionLost
    ConneXionLost is offline
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Using IFAND Statements

    Hi Lisa,

    Are you suggesting that the data in A2 should be the same as A1? If so, then try this in A2

    =IF(A1="","",A1)
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.
    Reply With QuoteRegister To Reply
  4. 10-15-2012, 08:31 AM #4
    hettbarn
    hettbarn is offline
    Registered User
    Join Date
    08-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Using IFAND Statements

    Sorry I was not clear. A1 is a date (say of return) and A2 is the reason code for the return (say wrong size). So, I want to validate that if there is a date in A1 there is also a reason code in A2 - but if there is no date in A1 then A2 should also be blank.

    Thanks!
    Lisa
    Reply With QuoteRegister To Reply
  5. 10-15-2012, 08:47 AM #5
    dwint
    dwint is offline
    Forum Contributor
    Join Date
    10-02-2012
    Location
    Bumi Nusantara
    MS-Off Ver
    Excel 2010; Excel 2016
    Posts
    136

    Re: Using IFAND Statements

    please see attached file
    hope this is what you want
    Attached Files Attached Files
    click the star if it solves your problem
    Reply With QuoteRegister To Reply
  6. 10-15-2012, 09:34 AM #6
    hettbarn
    hettbarn is offline
    Registered User
    Join Date
    08-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Using IFAND Statements

    This is sooooo close ... the only thing I am missing is that I want it to tell me that it is also OK if there is no date and no reason code (currently is coming back with "#VALUE").

    Thank you for all of your help -- I would have NEVER gotten to that code on my own!!!
    Reply With QuoteRegister To Reply
  7. 10-15-2012, 09:41 AM #7
    dwint
    dwint is offline
    Forum Contributor
    Join Date
    10-02-2012
    Location
    Bumi Nusantara
    MS-Off Ver
    Excel 2010; Excel 2016
    Posts
    136

    Re: Using IFAND Statements

    then.. change the code in my sample with this one
    =IF(OR((A1)*(A2<>""),(A1="")*(A2="")),"OK",ADDRESS(ROW(A2),COLUMN(A2),4)&" should be blank")
    Reply With QuoteRegister To Reply
  8. 10-15-2012, 09:59 AM #8
    hettbarn
    hettbarn is offline
    Registered User
    Join Date
    08-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Using IFAND Statements

    It is still bringing back "#VALUE" where both cells are blank...
    Reply With QuoteRegister To Reply
  9. 10-15-2012, 10:06 AM #9
    Kevin UK
    Kevin UK is offline
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: Using IFAND Statements

    Hi

    If the date is in A1 then in A2 how about:
    Formula: copy to clipboard 
    =IF(ISNUMBER(A1),"Wrong size","")
    Reply With QuoteRegister To Reply
  10. 10-15-2012, 10:42 AM #10
    hettbarn
    hettbarn is offline
    Registered User
    Join Date
    08-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Using IFAND Statements

    This is close as well ... but does tell me "wrong size" when there is a date but no reason code. I just want it to validate if there is a date then there is also a reason code ... so it is OK to not have both - but if one of them (i.e. date or reason code) is present then they should both be present.
    Reply With QuoteRegister To Reply
  11. 10-15-2012, 10:11 AM #11
    dwint
    dwint is offline
    Forum Contributor
    Join Date
    10-02-2012
    Location
    Bumi Nusantara
    MS-Off Ver
    Excel 2010; Excel 2016
    Posts
    136

    Re: Using IFAND Statements

    does it still give error result?
    see attached
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 10-15-2012, 10:39 AM #12
    hettbarn
    hettbarn is offline
    Registered User
    Join Date
    08-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Using IFAND Statements

    Yes - still an error result - but I don't see any differences between the last attachment and the previous code...
    Reply With QuoteRegister To Reply
  13. 10-15-2012, 11:09 AM #13
    dwint
    dwint is offline
    Forum Contributor
    Join Date
    10-02-2012
    Location
    Bumi Nusantara
    MS-Off Ver
    Excel 2010; Excel 2016
    Posts
    136

    Re: Using IFAND Statements

    try this one
    =IF(OR((A1<>"")*(A2<>""),(A1="")*(A2="")),"OK","One of this cell should be blank")
    Reply With QuoteRegister To Reply
  14. 10-15-2012, 11:16 AM #14
    hettbarn
    hettbarn is offline
    Registered User
    Join Date
    08-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Using IFAND Statements

    That's it! THANK YOU for all of your help!!!
    Reply With QuoteRegister To Reply
  15. 10-15-2012, 11:19 AM #15
    ConneXionLost
    ConneXionLost is offline
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Using IFAND Statements

    How abou this:

    =IF(AND(A1="",A2=""),"",IF(AND(A1<>"",A2<>""),"OK","Incomplete"))
    Reply With QuoteRegister To Reply
  16. 10-15-2012, 11:36 AM #16
    hettbarn
    hettbarn is offline
    Registered User
    Join Date
    08-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Using IFAND Statements

    This works too - thanks!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1