+ Reply to Thread
Results 1 to 12 of 12

Help with IF formula

  1. 07-20-2017, 03:46 AM #1
    annaandthering
    annaandthering is offline
    Registered User
    Join Date
    07-20-2017
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    9

    Help with IF formula

    I want to create a formula which autofills a cell with 1 of 4 options, when a date is added to the previous cell.

    B9 is the cell with the date data. These are my date ranges.
    19/06/2015 14/07/2016
    15/07/2016 03/11/2016
    04/11/2016 01/01/2020
    and blank if no date recorded

    This is the text for the 3 ranges
    v2 19 Jun 2015 RED
    v4 15 Jul 2016 GREEN
    v7 04 Nov 2016 BLUE

    =IF(AND(B9>="2015,6,9",B9<="2016,7,14"),"v2 19 Jun 2015 RED",IF(AND(B9>="2016,7,15,B9<=2016,11,3"),"v4 15 Jul 2016 GREEN",IF(AND(B9>=”2016,11,4”,B9<=”2020,1,1”),"v7 04 Nov 2016 BLUE“,IF(AND(B9 = “ “), “ “))))

    Are my brackets too few or too numerous? Should it be OR not AND - should it be neither.
    Reply With QuoteRegister To Reply
  2. 07-20-2017, 03:51 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,639

    Re: Help with IF formula

    Welcome to the forum! There are better ways of doing this. Please attach a sample workbook here and I will show you how.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 07-20-2017, 04:09 AM #3
    annaandthering
    annaandthering is offline
    Registered User
    Join Date
    07-20-2017
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    9

    Re: Help with IF formula

    Here it be. Thank you
    Attached Files Attached Files
    Last edited by annaandthering; 07-20-2017 at 04:10 AM. Reason: review
    Reply With QuoteRegister To Reply
  4. 07-20-2017, 04:12 AM #4
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,639

    Re: Help with IF formula

    In C9 copied down:

    =IFERROR(INDEX($Q$3:$Q$5,MATCH(B9,$O$3:$O$5,1)),"")
    Reply With QuoteRegister To Reply
  5. 07-20-2017, 04:23 AM #5
    annaandthering
    annaandthering is offline
    Registered User
    Join Date
    07-20-2017
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    9

    Re: Help with IF formula

    Thank you!

    So helpful! Very new to formulae in Excel so much to learn. If you have a moment, are you able to explain why my formula wouldn't work?

    Again,
    Thank you.
    Reply With QuoteRegister To Reply
  6. 07-20-2017, 04:27 AM #6
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,639

    Re: Help with IF formula

    It would take too long - too many things wrong with it, including inverted commas in the wrong place and date references that simply won't work. I'd be happier explaining the formula I've offered so you can reuse that in future, if you'd like.
    Reply With QuoteRegister To Reply
  7. 07-20-2017, 04:30 AM #7
    annaandthering
    annaandthering is offline
    Registered User
    Join Date
    07-20-2017
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    9

    Re: Help with IF formula

    Oh dear! Ha ha!

    You formula makes perfect sense though! I will use cell references instead of text and dates in future!
    Reply With QuoteRegister To Reply
  8. 07-20-2017, 04:31 AM #8
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,639

    Re: Help with IF formula

    Glad to have helped.
    Reply With QuoteRegister To Reply
  9. 07-20-2017, 05:31 AM #9
    annaandthering
    annaandthering is offline
    Registered User
    Join Date
    07-20-2017
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    9

    Re: Help with IF formula

    Can I ask one final question.

    Are you able to edit the formula so that if the incorrect version of consent has been used you can manually updated to the version used.

    eg. it autofills with v4 but actually v2 was signed. Is there a way to allow a manual edit without it being overwritten once entered?

    Thanks again!
    Reply With QuoteRegister To Reply
  10. 07-20-2017, 05:54 AM #10
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,639

    Re: Help with IF formula

    Yes - you can manually overwrite any formula cell without affecting the rest of the column. However, once done, the manual edit takes precedence and will not change. To get rid of it, you'd have to copy the formula down back over it again. Hope this makes sense!
    Reply With QuoteRegister To Reply
  11. 07-20-2017, 06:04 AM #11
    annaandthering
    annaandthering is offline
    Registered User
    Join Date
    07-20-2017
    Location
    London, England
    MS-Off Ver
    2013
    Posts
    9

    Re: Help with IF formula

    Oh I see, you need to delete the formula and then continue. Thank you!
    Reply With QuoteRegister To Reply
  12. 07-20-2017, 06:06 AM #12
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,639

    Re: Help with IF formula

    No, just type over it - click on the cell and type, or paste in another value.
    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)

Similar Threads

  1. Excel formula bar to display the result of the formula , not the formula?
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2016, 07:15 PM
  2. Copy Value String text of Formula become Formula but the formula not automatically active
    By Yovan Wisnu in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  3. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

Tags for this Thread

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