Results 1 to 3 of 3

Thread: IF FUNCTION WITH DATA VALIDATION (ie, if C3 has data, F3 must be completed)

  1. #1

    IF FUNCTION WITH DATA VALIDATION (ie, if C3 has data, F3 must be completed)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi guys,

    I've got a basic spreadsheet (attached) with data validation and depending on what is input into a certain cell, I wanted a few different things to happen:

    1. Column F from F3 to F300 must have some data input if column C has a date in it. ie, IF C3 has a date then F3 must be completed and so on C4 THEN F4 must be completed etc.
    2. IF C3 has a date, either H3, I3 or J3 must be completed (but only one out of the 3) and so on down the list
    3. IF H3, I3 or J3 is completed then K3 must be completed and so on down the list
    4. IF K3 is completed, L3 automatically shows 'Yes'. If not, show No and so on down the list.

    Hope this can be done,

    Thanks in advance
    Craig.
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,089
    Articles
    79
    Blog Entries
    14
    Okay, for the first 3, I'd use conditional formatting. You'll want to:
    • Select F3:F302 and create a new conditional formatting rule based on a formula
    • Formula: =AND($C3>0,LEN($F3)=0)
    • Set the condition you want by clicking on Format (maybe a red background?)

    • Select H3:J302 and create a new conditional formatting rule based on a formula
    • Formula: =AND($C3>0,COUNTA($H3:$J3)<>1)
    • Set the condition you want by clicking on Format (maybe a red background?)

    • Select K3:K302 and create a new conditional formatting rule based on a formula
    • Formula: =AND(COUNTA($H3:$J3)=1,LEN($K3)=0)
    • Set the condition you want by clicking on Format (maybe a red background?)

    For the last one, just use the formula (in cell L3) and copy it down: =IF(LEN(K3)>0,"Yes","No")
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Thanks very much, works like a treat

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •