+ Reply to Thread
Results 1 to 13 of 13

Populate same Cell with 2 possible out comes with If(isblank)

  1. #1
    Registered User
    Join Date
    02-18-2021
    Location
    Bamber Bridge
    MS-Off Ver
    Microsoft 365
    Posts
    17

    Populate same Cell with 2 possible out comes with If(isblank)

    Hi,
    I am trying to populate a cell (R3) with 2 possible out comes, although I can't work out how to do it. Both formulas are below and are required in cell (R3). Basically if (T3) is populated with a date (R3) returns a completed statement, but I also require (R3) to return a blank cell if (S3) is not populated with a date.

    =IF(ISBLANK($T3),$S3+365,"Completed")
    =IF(ISBLANK($S3),"",$S3+365)

    Any help would be appreciated.

    Kevin.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,644

    Re: Populate same Cell with 2 possible out comes with If(isblank)

    Welcome to the forum.

    Makes no sense! If S3 is blank, you want to add 365 to S3??? How when there's nothing there???

    Try again ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,607

    Re: Populate same Cell with 2 possible out comes with If(isblank)

    So only if there are dates in both S&T you want R to return "complete", and in other scenarios R will remain blank?

    try this:
    =IF(AND(ISNUMBER(T3),(ISNUMBER(S3))),"completed","")

  4. #4
    Registered User
    Join Date
    02-18-2021
    Location
    Bamber Bridge
    MS-Off Ver
    Microsoft 365
    Posts
    17

    Re: Populate same Cell with 2 possible out comes with If(isblank)

    Hi AliGW,
    Thanks for looking at this, but I require the Eye sight due box (R3) to either display complete if there as been a test taken in (T3) or to display nothing if the last eye sight test (S3) as not been yet been populated. At the moment I can do one or the other but not both in the same cell. I have tried to simplify it by keeping it on one sheet but there are sheets for say next year and of course the eye sight test hasn't been done yet!

    Hope that explains it better!
    Once again thank you,
    Kevin.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,644

    Re: Populate same Cell with 2 possible out comes with If(isblank)

    OK.

    What happens if:

    Both S3 and T3 are blank?
    Both S3 and T3are populated?

    Which needs checking first?

    What happens if none of the criteria are met?

  6. #6
    Registered User
    Join Date
    02-18-2021
    Location
    Bamber Bridge
    MS-Off Ver
    Microsoft 365
    Posts
    17

    Re: Populate same Cell with 2 possible out comes with If(isblank)

    If both S3 and T3 are blank then R3 is displaying 30/12/1900 with the formula =IF(ISBLANK($T3),$S3+365,"Complete")
    If both S3 and T3 are populated R3 is displaying Complete with the same formula

    The last eye sight test (S3) will require checking first.

    If none of the criteria are met then then it's just the same if S3 and T3 are blank R3 auto populates the formula with a figure of 30/12/1900 which I want to be blank.

    Thanks.

  7. #7
    Registered User
    Join Date
    02-18-2021
    Location
    Bamber Bridge
    MS-Off Ver
    Microsoft 365
    Posts
    17

    Re: Populate same Cell with 2 possible out comes with If(isblank)

    Thanks for replying Belinda, but I still require the formula of S3+365 to be displayed in the Eye Sight Test R3 cell. Everything else did work. So thanks. I just that formula in there somehow.

    Kevin.

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,644

    Re: Populate same Cell with 2 possible out comes with If(isblank)

    OK - how about this?

    =IF(AND(S3="",T3=""),"",IF(T3="",$S3+365,"Complete"))

  9. #9
    Registered User
    Join Date
    02-18-2021
    Location
    Bamber Bridge
    MS-Off Ver
    Microsoft 365
    Posts
    17

    Re: Populate same Cell with 2 possible out comes with If(isblank)

    Wow!
    Thanks for this it works a treat. I will have to work out how the formula works now! I don't just like to copy.

    Much appreciated!!

    Kevin.

  10. #10
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,581

    Re: Populate same Cell with 2 possible out comes with If(isblank)

    I suppose you are adding 365 to add a year. Is it important that leap years be taken into account ? Or should the date be a weekday ? ( I guess not, just to be sure)

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,644

    Re: Populate same Cell with 2 possible out comes with If(isblank)

    Pepe has a point. This might be more reliable:

    =IF(AND(S3="",T3=""),"",IF(T3="",EDATE($S3,12),"Complete"))

  12. #12
    Registered User
    Join Date
    02-18-2021
    Location
    Bamber Bridge
    MS-Off Ver
    Microsoft 365
    Posts
    17

    Re: Populate same Cell with 2 possible out comes with If(isblank)

    Hi,
    So I assume the ,12 after EDATE refers to 12 months? I have just looked up the EDATE function and I didn't know about that. Looks good so I will put that into my spreadsheet. Thanks for all your help! I was banging my head against a brick wall before this.

    Kevin.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,644

    Re: Populate same Cell with 2 possible out comes with If(isblank)

    Yes, that's right.

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

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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] Cell with 0 characters but FALSE =ISBLANK
    By SHI.NL in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-19-2018, 05:42 AM
  2. [SOLVED] ISBLANK therefore go to next cell
    By KDHY in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-31-2015, 02:52 PM
  3. [SOLVED] isblank inappropriate for more than 1 cell?
    By Butcher1 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-11-2014, 03:24 PM
  4. ISBLANK w/ Formula in target cell
    By grantpank in forum Excel General
    Replies: 2
    Last Post: 08-29-2011, 06:12 PM
  5. Help with ISBLANK function referring to a cell with another ISBLANK formula
    By camdameron in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-23-2011, 12:45 PM
  6. ISBLANK function and Cell range
    By AndyF19 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-19-2010, 09:33 AM
  7. Can you test for a range (Q16:19) any cell is ISBLANK
    By CRayF in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-25-2005, 02:05 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