|
Calculate to another cell as default value
Hi.
Given the following: A B C 1 5 9 __ 2 8 10 __ I'd like for the user to enter values in A and B and have Excel display B minus A in C as a DEFAULT value. That is, for row 1, once the user enters 9 in B, I want to display 4 in C but give the user the option to override the calculation. A formula in C won't work because the user would delete the formula if he overrides it. Thanx. I don't |
Why not use an extra column (C) and have this formula in D:
=IF(C1="",B1-A1,C1) -- Kind regards, Niek Otten Microsoft MVP - Excel "Hector" wrote in message ... Hi. Given the following: A B C 1 5 9 __ 2 8 10 __ I'd like for the user to enter values in A and B and have Excel display B minus A in C as a DEFAULT value. That is, for row 1, once the user enters 9 in B, I want to display 4 in C but give the user the option to override the calculation. A formula in C won't work because the user would delete the formula if he overrides it. Thanx. I don't |
That's a good idea. Though, I would like for the user to enter a value only
in exceptions. This method would make the user do the calculation which will be more complicated. Thanx. "Niek Otten" wrote: Why not use an extra column (C) and have this formula in D: =IF(C1="",B1-A1,C1) -- Kind regards, Niek Otten Microsoft MVP - Excel "Hector" wrote in message ... Hi. Given the following: A B C 1 5 9 __ 2 8 10 __ I'd like for the user to enter values in A and B and have Excel display B minus A in C as a DEFAULT value. That is, for row 1, once the user enters 9 in B, I want to display 4 in C but give the user the option to override the calculation. A formula in C won't work because the user would delete the formula if he overrides it. Thanx. I don't |
I don't see what you mean. If the users enters nothing in C, Excel will
subtract B from A. Only if he enters something in C, then that will be used. Do I miss something? -- Kind regards, Niek Otten Microsoft MVP - Excel "Hector" wrote in message ... That's a good idea. Though, I would like for the user to enter a value only in exceptions. This method would make the user do the calculation which will be more complicated. Thanx. "Niek Otten" wrote: Why not use an extra column (C) and have this formula in D: =IF(C1="",B1-A1,C1) -- Kind regards, Niek Otten Microsoft MVP - Excel "Hector" wrote in message ... Hi. Given the following: A B C 1 5 9 __ 2 8 10 __ I'd like for the user to enter values in A and B and have Excel display B minus A in C as a DEFAULT value. That is, for row 1, once the user enters 9 in B, I want to display 4 in C but give the user the option to override the calculation. A formula in C won't work because the user would delete the formula if he overrides it. Thanx. I don't |
You're right. I didn't read your post carefully. Sorry.
It's that I was looking for a another solution that did not involve formulas because the user has the ability to delete unprotected rows. I'm trying some VB I looked up earlier but am not being able to calculate and display where I want to. "Niek Otten" wrote: I don't see what you mean. If the users enters nothing in C, Excel will subtract B from A. Only if he enters something in C, then that will be used. Do I miss something? -- Kind regards, Niek Otten Microsoft MVP - Excel "Hector" wrote in message ... That's a good idea. Though, I would like for the user to enter a value only in exceptions. This method would make the user do the calculation which will be more complicated. Thanx. "Niek Otten" wrote: Why not use an extra column (C) and have this formula in D: =IF(C1="",B1-A1,C1) -- Kind regards, Niek Otten Microsoft MVP - Excel "Hector" wrote in message ... Hi. Given the following: A B C 1 5 9 __ 2 8 10 __ I'd like for the user to enter values in A and B and have Excel display B minus A in C as a DEFAULT value. That is, for row 1, once the user enters 9 in B, I want to display 4 in C but give the user the option to override the calculation. A formula in C won't work because the user would delete the formula if he overrides it. Thanx. I don't |
Paste this into your Sheet module:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Columns(2)) Is Nothing Then Target.Offset(, 1).Value = (Target.Value - Target.Offset(, -1).Value) End If End Sub HTH, "Niek Otten" wrote in message ... Why not use an extra column (C) and have this formula in D: =IF(C1="",B1-A1,C1) -- Kind regards, Niek Otten Microsoft MVP - Excel "Hector" wrote in message ... Hi. Given the following: A B C 1 5 9 __ 2 8 10 __ I'd like for the user to enter values in A and B and have Excel display B minus A in C as a DEFAULT value. That is, for row 1, once the user enters 9 in B, I want to display 4 in C but give the user the option to override the calculation. A formula in C won't work because the user would delete the formula if he overrides it. Thanx. I don't |
It worked. Thanx.
"Jim May" wrote: Paste this into your Sheet module: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Columns(2)) Is Nothing Then Target.Offset(, 1).Value = (Target.Value - Target.Offset(, -1).Value) End If End Sub HTH, "Niek Otten" wrote in message ... Why not use an extra column (C) and have this formula in D: =IF(C1="",B1-A1,C1) -- Kind regards, Niek Otten Microsoft MVP - Excel "Hector" wrote in message ... Hi. Given the following: A B C 1 5 9 __ 2 8 10 __ I'd like for the user to enter values in A and B and have Excel display B minus A in C as a DEFAULT value. That is, for row 1, once the user enters 9 in B, I want to display 4 in C but give the user the option to override the calculation. A formula in C won't work because the user would delete the formula if he overrides it. Thanx. I don't |
| All times are GMT +1. The time now is 08:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com