Excel - changing cell value depending on other cell value

[Nikox9]

Hello,

I'd like to modify a cell A1 (= xxx) depending on A2 (drop-down list = 10, 100, 1000, 10000...) as follow:

--> select A1 and implement the following format:
if(A2 = 10, A1 = A1/10)
if(A2 = 100, A1 = A1/100)
if(A2 = 1000, A1 = A1/1000)
...

Actually, selecting a number in the list A2 make the content of A1 change accordingly, but using the previous value present in A1.

If anyone could help me...

Thanks,
Nikox.

[TheShaner]

The way excel works, you can't have a formula in a cell along with the ability to type in a value in the same cell. A cell either accepts a formula or a value and not both. Second, all your IF statements can be replaced with this formula: =A1/A2. And last, why don't you just set it up where A1 accepts the value you type in, A2 is the dropdown, and A3 is the formula I gave you?

If you really want it where A1 accepts the typed in value and then changes based on A2's selection, you will have to jump into VBA code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = 2 And Target.Column = 1 Then
Me.Range("A1").Value = Me.Range("A1").Value / Target.Value
End If

End Sub
The above code is triggered when there is a change in the worksheet. The IF statement checks to see if that change was with A2 (row 2, column 1). If it is, then A1's value is divided by A2's, and set appropriately.

The only problem with this is how does the user know if the division has occurred or not? What if the user changes the dropdown twice in a row or more? The previous value from the previous division is then used. That's why I suggest the first solution of using a third cell to show the result.

-Shane

[Nikox9]

Alright. I will then use a third cell in another worksheet. I did not want to have too many similar numbers in the same worksheet. Thanks.

I would have another question. I have created some groups you can expand/collapse with "+"/"-" and I'd like to protect from writing/editing every cell except some columns, but then when I unlock only these, I cannot expand/collapse anymore since the sheet is protected !!

Any idea ?

Thanks,
Nikox.