ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   subtracting from multiple cells (https://www.excelbanter.com/excel-discussion-misc-queries/169471-subtracting-multiple-cells.html)

He cries for help

subtracting from multiple cells
 
I am trying to subtract a given number from numerous cells i.e. A1,A2,A3,-6.
I want to do this from a remote cell by just entering a number. This cell
will be
used to update these cells (i.e. A1,A2,A3) on a daily basis.

The target cells should update with each entry.

Thanks in advance, as you can tell I am a Novice. I am more of a access user.

Otto Moehrbach

subtracting from multiple cells
 
Assuming the range you want to change is A1:A6 and the remote cell is H1 and
they are all on the same sheet, the following macro does what you want.
Note that this macro is a sheet macro and must be placed in the sheet module
of the sheet in question. To access that sheet, right-click on the sheet
tab, select View Code. Paste this macro into the displayed module. "X" out
of the module to return to the sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "H1" Then
Target.Copy
Range("A1:A6").PasteSpecial Paste:=xlPasteAll,
Operation:=xlSubtract
End If
End Sub

"He cries for help" <He cries for wrote in
message ...
I am trying to subtract a given number from numerous cells i.e.
A1,A2,A3,-6.
I want to do this from a remote cell by just entering a number. This cell
will be
used to update these cells (i.e. A1,A2,A3) on a daily basis.

The target cells should update with each entry.

Thanks in advance, as you can tell I am a Novice. I am more of a access
user.




Tim

subtracting from multiple cells
 
Hi,

One way by using formula. Create a helper column lets say in column C with
the data which is in A1,A2€¦.
In H1 is the remote cell by which you want to subtract a given number. Put
in A1 this formula =C1-H$1 and copy down.
You can either hide or put the helper column in a different sheet if you want.

Regards,

Tim


"He cries for help" wrote:

I am trying to subtract a given number from numerous cells i.e. A1,A2,A3,-6.
I want to do this from a remote cell by just entering a number. This cell
will be
used to update these cells (i.e. A1,A2,A3) on a daily basis.

The target cells should update with each entry.

Thanks in advance, as you can tell I am a Novice. I am more of a access user.



All times are GMT +1. The time now is 12:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com