# Blank out cells when cells have an input

1. ## 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

2. ## 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.

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.

3. ## Re: Blank out cells when cells have an input

Maybe try:
Formula:
`Please Login or Register  to view this content.`

4. ## 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. ## 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. ## 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.

7. ## 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. ## Re: Blank out cells when cells have an input

Originally Posted by davsth
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. ## Re: Blank out cells when cells have an input

Originally Posted by davsth
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. ## 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. ## 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.

12. ## 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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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