View original (French)

Clear a cell based on the content of another

ninie_21_3 Posted messages 46 Status Member -  
ninie_21_3 Posted messages 46 Status Member -
Hello,

My Excel file has many cells and formulas, some of which were largely created by members of this forum (whom I sincerely thank!).

Currently, three things happen automatically:
- in one tab, I say for example in cell B2 = "to be checked"
- in that case, cell C3 turns red
- and then cell C4 gives access to a drop-down list (so far empty) thanks to data validation "INDIRECT(C3)" and I can choose between "to change" or "under repair"

Everything works very well.

The problem is that if I delete or change the status of cell B2, while my cell C3 turns white again, my cell C4 does not return to empty and that messes everything up...

How can I fix this?

3 answers

  1.
    via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Hello

    For now, without seeing the file, I already see 2 possibilities:

    1) Create a macro that clears the data in C4 when B2 changes

    2) Add a cell with a dropdown list indicating the final status: either nothing as long as it’s not resolved, or "changed", or "repaired".
    This latter option has the advantage of keeping a record of what has been done, the cell C4 remains with what needed to be done, "to be repaired" for example, and the second cell indicates that it has been done: "repaired"

    Regards

    --
    "Imagination is more important than knowledge." A. Einstein
    0
    1. ninie_21_3 Posted messages 46 Status Member
       
      Hello,

      Here is the document in question: https://www.cjoint.com/?3Jjn7pDrhYk

      As you will see, my first presentation had greatly simplified it.

      Basically, in the "Tech services" tab, all the odd rows (B5, C5, etc.) should be set to zero if the cell above is not red. I tried to create a formula, but it crashes everything and I can't come up with an acceptable macro.

      I don't want any history, so there's no need to add an extra cell.

      Thank you for your ideas, I'm totally stuck...
      0
  2. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Re

    Macro to be placed in the Worksheet of sheet7 tech services: 
    Private Sub Worksheet_Change(ByVal Target As Range) x = Target.Row y = Target.Column ' if column 1 or odd row exit If y = 1 Or x Mod 2 <> 0 Then Exit Sub 'otherwise clear below row Cells(x + 1, y) = "" End Sub


    With each modification in an even row starting from column B, we clear the row below
    x Mod 2 returns the remainder of the division of x (the row number) by 2, if it's 0 then x is even otherwise x is odd (in this case x Mod 2 <> 0 we exit the sub without clearing)

    Best regards

    --
    "Imagination is more important than knowledge." A. Einstein
    0