+ Reply to Thread
Results 1 to 9 of 9

hiding conditional format when activating cell is empty

  1. #1
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    hiding conditional format when activating cell is empty

    Sorry for that title but this is difficult to explain. I have uploaded a sample workbook to show it better.
    I'm refering to the small blue info box on the right of sheet 1 and also the duplicate on sheet 2.
    The left side of the box is where I will insert qualification, and the left mostly are the expiry dates. The details are then replicated in other worksheets as in sheet 2.
    I have conditionally formatted column 'X' to 'ALERT' when the date is within 60 days of the expiry date (that bit works OK)
    but
    if the master info block (sheet 1) doesn't have a date in it down the righthand side, two things happen
    1. , the subseqeunt box on the following sheet show the date 0 January 1901 with the 'Alert' flag showing alongside it.
    2. The 'Alert' warning flag shows on the master info block (sheet 1)
    I would like to hide the 'Alert' and the 1901 date if possible when there is no date in the righthand column.
    I hope you can follow this pathetic attempt at explaining the problem.
    Additinally for some unknown reason a '0' as appeared in column 'U' on sheet two if you could also advise on that.
    Thank you very much
    Attached Files Attached Files
    Last edited by nje; 07-27-2010 at 04:19 PM.

  2. #2
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: hiding conditional format when activating cell is empty

    Select H5:H12 in 2nd sheet and then go to conditional formatting. Erase everything there and use this one

    =AND(NOT($E5=""),$F5-60<TODAY())

    WB is also attached
    Is that what you're looking for???..

    HTH

    Attached Files Attached Files
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, ?Born in USSR?
    Vusal M Dadashev

    Baku, Azerbaijan

  3. #3
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    Re: hiding conditional format when activating cell is empty

    Thanks HTH, In answer to your question, yes that's what I'm looking for, but I couldn't get it to work on my copy so I looked at how you did it and have cocked it up a bit (sheet 2 H11) I'm not sure what I've done but it won't work. I tried entering your formula into the conditional format and pulling it down from the others above, but failed, There are quite a few other headings and dates to go in the box so I would like to be able to understand what I've done wrong.
    I don't know how you got rid of the '0' in column 'U' but thanks again for that.

    My problems are still on sheet one with the 'Alert' flag still showing if there is no date in it, but what you have achieved in such a short time on sheet two is great I have upload the two sheets you have worked on to show you how I cocked it up(sorry) I have attached the cocked up version
    Attached Files Attached Files
    Last edited by nje; 07-26-2010 at 01:37 PM. Reason: forgot attachment

  4. #4
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    Re: hiding conditional format when activating cell is empty

    Having got up early this morning with a fresh mind, I have made some progress, I still don't understand what this means exactly and my book doesn't help me much in deciphering it. =AND(NOT($E5=""),$F5-60<TODAY()) I have got it in place though.

    I have managed to resolve the problem of the date 00 January 1900 that appears in Coloumn F sheet 2, when column V7 to 17 in sheet 1 are left empty.
    I did this by setting up a conditional format to change the text to the bacground colour if it saw a date prior to 01 January 1900. This seems to do the job OK. but I really wanted it to be blank like its master on sheet 1.
    I still have the problem though of my alert boxes, Is there a formula that would keep them from activating, until the cells are populated. I have tried lots of formulas, but I don't really understand them that well yet, so haven't got there yet.
    I have attached the workbook sample
    Thanks
    Attached Files Attached Files
    Last edited by nje; 07-27-2010 at 04:04 AM. Reason: spelling errors

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: hiding conditional format when activating cell is empty

    AND(NOT($E5="", is the same as
    AND($E5<>"",
    for your other bit
    just use in F7
    =IF(Sheet1!V$7="","",Sheet1!V$7) no need for cond.format then
    Last edited by martindwilson; 07-27-2010 at 04:20 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  6. #6
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    Re: hiding conditional format when activating cell is empty

    Thanks very much Martin, that works a treat for keeping sheet 2 columns clear and all the alerts still work. I have attached the results,

    I just need to stop the alerts on Sheet 1 showing up when the column V is empty now if you wouldn't mind advising.
    The shortened formula you provided AND($E5<>"", this is the bit I don't understand, what is it designed to do?

    Thanks again for you help very much appreciated.
    Attached Files Attached Files

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: hiding conditional format when activating cell is empty

    <> means does not equal so e5<>"" means if e5 isn't blank
    what is this suppose to do?
    =" Alert "&TEXT(V7,)?

  8. #8
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    Re: hiding conditional format when activating cell is empty

    Thanks for that info, very useful, must rush got to go to the dentist

  9. #9
    Valued Forum Contributor
    Join Date
    06-23-2010
    Location
    North West
    MS-Off Ver
    office 2010
    Posts
    355

    Re: hiding conditional format when activating cell is empty

    Thank you both for your formulas, it's taken a long time for me to understand it and put everything where it was needed but at last I've done it. Blank cells where I want them, no intrusive warnings or odd dates popping up,. It's really quite easy once you have finished it, but although the journey was long, I enjoyed learning quite a lot as I did it
    Thanks again for your help,

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1