Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,090
Default 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.



  #3   Report Post  
Posted to microsoft.public.excel.misc
Tim Tim is offline
external usenet poster
 
Posts: 408
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
What formula is used for subtracting a range of different cells f. tim Excel Worksheet Functions 3 April 21st 23 10:07 PM
Subtracting days from cells B J G[_2_] Excel Worksheet Functions 1 October 18th 07 10:23 PM
subtracting multiple values between sheets peter Setting up and Configuration of Excel 3 June 4th 06 09:09 PM
Subtracting From 2 Cells scw1217 Excel Worksheet Functions 2 April 5th 06 01:56 AM
Subtracting 2 Now() Cells rich Excel Worksheet Functions 0 February 8th 06 09:10 PM


All times are GMT +1. The time now is 02:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"