+ Reply to Thread
Results 1 to 12 of 12

Blank out cells when cells have an input

  1. #1
    Registered User
    Join Date
    07-09-2012
    Location
    Scotland
    MS-Off Ver
    MS 365 Excel
    Posts
    7

    Blank out cells when cells have an input

    I have a worksheet where there is 5 rows in a column, I have a formula which I am currently using which when an entry as been input into cell H9 of the column it greys out cells H10, H11, H12 & H13, this formula is =$H$9<>"" however this formula only works when there is an entry in H9, is there a formula which can be used to blank out any of the 4 cells from H9 to H13 when an entry has been put into any of the said cells.

    Also on another column I also have the same problem but this time it requires any 2 cells of the 5 to have input into the cell and blank out the remaining 3.

    Do I need to look at IF & AND formulas? Any tips would be appreciated
    Attached Images Attached Images

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,469

    Re: Blank out cells when cells have an input

    There are instructions at the top of the page explaining how to attach your sample workbook.

    Administrative Note

    Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,252

    Re: Blank out cells when cells have an input

    Maybe try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,607

    Re: Blank out cells when cells have an input

    You can use COUNTA to count the number of non-blank cells in that range, so:

    =COUNTA($H$9:$H$13)>0

    for any entry into the range, or:

    =COUNTA($H$9:$H$13)>1

    for 2 cells, and so on.

    You can combine this with AND if you don't want to blank out the cells(s) with the data.

    Hope this helps.

    Pete

  5. #5
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,994

    Re: Blank out cells when cells have an input

    I think it is a combination as you do not blank the cell with a value in and(COUNTA($H$9:$H$13)>0,H9="")

    which I now see Pete suggested anyway!

  6. #6
    Registered User
    Join Date
    07-09-2012
    Location
    Scotland
    MS-Off Ver
    MS 365 Excel
    Posts
    7

    Re: Blank out cells when cells have an input

    Thanks for the replies from all, I have updated my profile to show MS 365 excel is the current files I am working on. I quickly tried the count formulas and what this does is blanks all 5 out which is not what I am looking at. I have uploaded an example sheet for review, this is column H is the once per day and column M is the twice per day.

    So currently our worksheet is set up so that when an Item ID is entered into A4, the whole row 4 is highlighted to say these are mandatory tests.

    If I enter into H4 test carried out on first one the cell with the data input then changes to white (which is what we want it to do) then it blanks out the remaining 4 cells, as this has now been captured.
    But what if the test 4 was completed on the second item ID I can't get the formula to recognise that its been entered on another row and grey out the remainder 4, I can only get this to work if its done on the first item each day.

    I hope I have managed to upload this correctly this time instead of pictures and I appreciate your patience with me on this.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,994

    Re: Blank out cells when cells have an input

    Perhaps along the same lines that we have said
    =AND(COUNTA(C$4:C$8),C4="",$A4<>"") as a second formula and format grey

  8. #8
    Registered User
    Join Date
    07-09-2012
    Location
    Scotland
    MS-Off Ver
    MS 365 Excel
    Posts
    7

    Re: Blank out cells when cells have an input

    Quote Originally Posted by davsth View Post
    I think it is a combination as you do not blank the cell with a value in and(COUNTA($H$9:$H$13)>0,H9="")

    which I now see Pete suggested anyway!
    I tried the formula from Pete and that blanks all the cells

  9. #9
    Registered User
    Join Date
    07-09-2012
    Location
    Scotland
    MS-Off Ver
    MS 365 Excel
    Posts
    7

    Re: Blank out cells when cells have an input

    Quote Originally Posted by davsth View Post
    Perhaps along the same lines that we have said
    =AND(COUNTA(C$4:C$8),C4="",$A4<>"") as a second formula and format grey
    Now that works, for single tests, so whenever I enter in the number into any of the rows it is blanking the remaining 4.

    So to be a total pain in the behind, how would I get this to work on any two cells now and it blanks out 3? Sorry to be a pain, I have worked so hard on this worksheet and this formula has been the thorn in my side to complete.

  10. #10
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,994

    Re: Blank out cells when cells have an input

    and post 7 does that work?
    =AND(COUNTA(C$4:C$8),C4="",$A4<>"") as a second formula and format grey

    if the row has an entry in the first column and there are values in the column of interest and the cell in question does not contain a value format grey

    for 2 cell in a column
    =AND(COUNTA(C$4:C$8)>=2,C4="",$A4<>"") as a second formula and format grey if that is what you mean

    Otherwise populate a few columns of your spreadsheet and manually shade it as yo wish it to appear, then we can write the formula

  11. #11
    Registered User
    Join Date
    07-09-2012
    Location
    Scotland
    MS-Off Ver
    MS 365 Excel
    Posts
    7

    Re: Blank out cells when cells have an input

    I had am incorrect cell number in the two cell option is now working and complete, Davsth thank you very much for your help this will go a long way to getting my sheet approved and implemented into moving away from paper to a digital platform.
    Last edited by AliGW; 10-11-2021 at 06:03 AM. Reason: PLEASE don't quote unnecessarily!

  12. #12
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,994

    Re: Blank out cells when cells have an input

    I would also have a conditional format of
    COUNTA(C$4:C$8)>2 considered if this was an error? So it is highlighted

+ 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. Input Info from Column/Cells some blank some numbers
    By viclea in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-10-2020, 11:09 AM
  2. Replies: 12
    Last Post: 05-29-2018, 05:11 PM
  3. [SOLVED] Input a Formula into next set of blank cells
    By sherylt13 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-29-2016, 09:31 PM
  4. Count blank cells in a range and contiguous blank cells also as single cells! Tricky One!
    By SebastianColombia in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-31-2015, 02:32 PM
  5. Check particular blank cells in a row based on input in D column
    By weareallstardust in forum Excel General
    Replies: 1
    Last Post: 09-24-2014, 09:51 AM
  6. [SOLVED] Need Help Keeping Cells Blank Until Data is placed in Input Cell
    By areidel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-19-2012, 05:45 PM
  7. Replies: 5
    Last Post: 02-01-2010, 10:00 AM

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