+ Reply to Thread
Results 1 to 12 of 12

AND in Conditional Formatting Formula not working

  1. 11-30-2010, 03:26 PM #1
    readyemail
    readyemail is offline
    Registered User
    Join Date
    11-30-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    4

    AND in Conditional Formatting Formula not working

    Sorry, I posted this question elsewhere incorrectly. Here it is again:

    The following formula works just fine in any give cell:
    =IF(AND(INDIRECT(ADDRESS(ROW(),COLUMN(LE_WBSStartCol)))<=INDIRECT(ADDRESS(ROW(LE_PeriodEndRow),COLUMN())),INDIRECT(ADDRESS(ROW(),COLUMN(LE_WBSEndCol)))>=INDIRECT(ADDRESS(ROW(LE_PeriodStartRow),COLUMN()))),TRUE,FALSE)

    However, when applied as a conditional formatting formula, it doesn't appear to be working... I tried some variations of it but had the same result. Interestingly, if I split the two AND conditions (i.e., it into
    INDIRECT(ADDRESS(ROW(),COLUMN(LE_WBSStartCol)))<=INDIRECT(ADDRESS(ROW(LE_PeriodEndRow),COLUMN()))
    and (separately without using the AND operator)
    INDIRECT(ADDRESS(ROW(),COLUMN(LE_WBSEndCol)))>=INDIRECT(ADDRESS(ROW(LE_PeriodStartRow),COLUMN()))
    ) the conditional formatting works.
    FYI... LE_WBSStartCol, LE_WBSEndCol, LE_PeriodStartRow and LE_WBSEndCol are named ranges.

    Any help would be greatly appreciated. Thanks.
    Reply With QuoteRegister To Reply
  2. 11-30-2010, 04:01 PM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: AND in Conditional Formatting Formula not working

    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

    If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

    Doing this will ensure you get the result you need!
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 11-30-2010, 04:48 PM #3
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: AND in Conditional Formatting Formula not working

    I don't believe INDIRECT works in Conditional Formatting
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  4. 11-30-2010, 04:55 PM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: AND in Conditional Formatting Formula not working

    I think it does... you can indirectly reference a range on another sheet instead of naming the range, for example.
    Reply With QuoteRegister To Reply
  5. 11-30-2010, 05:06 PM #5
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: AND in Conditional Formatting Formula not working

    Nevermind, INDIRECT working for me now. Maybe I had manual calculations on. :-/
    Reply With QuoteRegister To Reply
  6. 11-30-2010, 05:06 PM #6
    readyemail
    readyemail is offline
    Registered User
    Join Date
    11-30-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: AND in Conditional Formatting Formula not working

    Thanks for responding... I've attached a stripped-down file. Please look at range I7-T142. The formula in the cells is working fine. The same formula in the Conditional Formatting is not. Thanks.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 11-30-2010, 05:14 PM #7
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: AND in Conditional Formatting Formula not working

    I don't know.. I am seeing a bunch of #N/A errors everywhere.

    What exactly are you trying to do?... why do you feel you need Indirect/Address instead of another lookup method?
    Reply With QuoteRegister To Reply
  8. 11-30-2010, 05:23 PM #8
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: AND in Conditional Formatting Formula not working

    Couldn't you use this formula in J7 copied across and down?

    =AND($H7<=J$4,$I7>=J$3)

    The same formula will work in conditional formatting
    Audere est facere
    Reply With QuoteRegister To Reply
  9. 11-30-2010, 05:28 PM #9
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: AND in Conditional Formatting Formula not working

    I was thinking along those lines too.

    Also, not sure what the Hlookup is supposed to be doing though? What is that formula in the top rows trying to find in column E of the other sheet?
    Reply With QuoteRegister To Reply
  10. 11-30-2010, 05:45 PM #10
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: AND in Conditional Formatting Formula not working

    I believe that gives the working hours between the dates above, so you could get the same result with this formula in J5 copied across

    =NETWORKDAYS(J3,J4)*9
    Reply With QuoteRegister To Reply
  11. 11-30-2010, 05:54 PM #11
    readyemail
    readyemail is offline
    Registered User
    Join Date
    11-30-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: AND in Conditional Formatting Formula not working

    I just reopened the sheet and don't see any #N/As... Please allow Macros and hit F9 if you are not getting the desired results. I know I could use direct cell reference but as the spreadsheet changes hands and gets modified, some of those reference become invalid --no matter how hard I tried to button it down. This is why I built formula's with minimum reference and all the "hard coded" references in Name Ranges.

    I've attached the a screen capture to show you what the results look like now. what I am trying to do is have the cells in range J7-T142 highlighted in yellow (using Conditional Formatting) when the date range in a given row in col H and I fall within the range of the corresponding dates in rows 3 and 4 (in respective cols J - T).

    The HLookup simply gets the values from the "LOOKUPS" sheet (the calculation is done in a user defined function which is working fine.)
    Last edited by readyemail; 11-30-2010 at 06:07 PM. Reason: Forgot to include attachment.
    Reply With QuoteRegister To Reply
  12. 11-30-2010, 06:00 PM #12
    readyemail
    readyemail is offline
    Registered User
    Join Date
    11-30-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: AND in Conditional Formatting Formula not working

    Quote Originally Posted by daddylonglegs View Post
    I believe that gives the working hours between the dates above, so you could get the same result with this formula in J5 copied across

    =NETWORKDAYS(J3,J4)*9
    Yes, I considered that option, but I needed to build a function that split periods in a certain fashion and - at least I thought - it was more efficient to do this calculation in the same pass and the splitting. I was really disappointed to fine the NETWORKDAYS didn't have an equivalent function in VBA (without add-ins) so I had to code a less the elegant function to figure out week day.
    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