+ Reply to Thread
Results 1 to 7 of 7

If functions and Conditional formatting

  1. #1
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Cleveland, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    116

    If functions and Conditional formatting

    Hi
    I have two issues with this workbook.
    Iím having problems with columns E and F with the If Functions.
    I would like the cells in E and F to return the formula value only if there is a value returned in the cells under column G, which does not fill after the end date (Cell B10). For Column F, it is returning an Error #Value!
    For Column E, I would like for the formula to return a value only if the corresponding cell in column G has returned a value, BUT with the next chronological number, and to be blank if there is no value returned in the corresponding cells in column G. So Cell E17 should be Ride 2.

    My other issue is that I would like any format of the mandatory dates (cells B11:B13) to be highlighted and my formula only highlights the full format.
    Can you please, please help me? And thank you too.
    Attached Files Attached Files

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: If functions and Conditional formatting

    The IF formulae in columns E and F don't make a lot of sense. You are not giving any condition for the IF function. You have a cell reference but lack what the condition is that you are looking for in that cell. That is where the whole series of IF formulae fail.

    F16 could have a formula like =IF(G16<>"","morning; afternoon","") which means that if G16 isn't blank return "morning; afternoon" and if G16 is blank return nothing.

    E16 could have a formula like =IF(G16<>"","Ride 1","") which means that if G16 isn't blank return "Ride 1" or if blank return nothing.
    E17 says to add 1 to the result in E16 but you are trying to do arithmetic with text. What should the result be in E17?

    E17 could possibly have a formula like this: =IF(G17<>"","Ride"&" "&RIGHT(E16)+1," ") this would result in Ride 2 if G17 was not blank.

    I'm just guessing at what you want. I really don't understand the significance of column F.

    The Conditional Formatting fails in column A because the contents of column A DO NOT match any of the cells from B11:B13. They contain the dates but they also contain other things as well....no match.
    Last edited by newdoverman; 08-15-2013 at 07:15 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    7,009

    Re: If functions and Conditional formatting

    Pl see attached file.
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Cleveland, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    116

    Re: If functions and Conditional formatting

    Hi
    First thank you so much for your quick response. I changed the formula and it worked fine, and also learned a little bit on the reasoning of the IF function, thanks to your explanation. The significance of column F is that it populates data in columns C & D.
    I uploaded the revised sheet. For column E, it is really important that for the numerals 1-9, they show as 01-09. Also, if you look at cell E26 (and following), the return goes back to Ride 1 for some reason.
    What I am looking for is for column E to fill only if there are values returned in column G, for the subsequent rows to always add 1 more to the number in the previous cell, and also to show as 01-99 and not 1-99.
    Attached Files Attached Files

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: If functions and Conditional formatting

    I inserted a hidden column G in order to get the numbers from 1-9 to show as you want. I also separated the date in column A that had the event with the date into column A with the event and column B with the date of the event. This will allow the Conditional Formatting that you wanted for this data.

    The formula has been corrected to stop the restarting of the numbering.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    02-25-2013
    Location
    Cleveland, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    116

    Red face Re: If functions and Conditional formatting

    Thank you so much. I updated my workbook with the hidden column G. I cannot split columns A-D as the results are used to populate other software.
    Appreciate all your help.

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: If functions and Conditional formatting

    Thanks for letting us know how you made out.

+ 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. Conditional Formatting/Functions
    By JessicaB517 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-21-2012, 02:22 PM
  2. IF Functions, Conditional Formatting
    By argegg26 in forum Excel General
    Replies: 3
    Last Post: 03-30-2010, 09:17 AM
  3. conditional formatting using IF with MID functions
    By jdubwelch in forum Excel General
    Replies: 6
    Last Post: 11-20-2007, 12:07 PM
  4. conditional formatting using IF with MID functions
    By jdubwelch in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-20-2007, 01:46 AM
  5. Conditional Formatting- Functions
    By jbroad in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-17-2007, 09:24 PM

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