sheet cell linking?

Hello,
I have sheet1 and sheet2.
In sheet1 (A1) I need to write number 5.
In sheet2 (A2) I need to write number 3.
Then, when I enter number 3 in sheet2 (A2) it automaticly needs to correct 
number 5 from sheet1
to number 2 ( so it does 5-3=2).
How can I do this?
Thanks in advance. 


0
8/20/2008 12:23:39 PM
excel 39879 articles. 2 followers. Follow

4 Replies
359 Views

Similar Articles

[PageSpeed] 17

Insert the following worksheet event macro in the Sheet2 code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set ws1 = Sheets("Sheet1")
Set s1a1 = ws1.Range("A1")
Set ws2 = Sheets("Sheet2")
Set s2a2 = ws2.Range("A2")
If Intersect(Target, s2a2) Is Nothing Then Exit Sub
Application.EnableEvents = False
    s1a1.Value = s1a1.Value - s2a2.Value
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


-- 
Gary''s Student - gsnu2007k


"KisH (Tihomir)" wrote:

> Hello,
> I have sheet1 and sheet2.
> In sheet1 (A1) I need to write number 5.
> In sheet2 (A2) I need to write number 3.
> Then, when I enter number 3 in sheet2 (A2) it automaticly needs to correct 
> number 5 from sheet1
> to number 2 ( so it does 5-3=2).
> How can I do this?
> Thanks in advance. 
> 
> 
> 
0
GarysStudent (1572)
8/20/2008 1:14:05 PM
"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message 
news:933DF882-BBB4-4FC5-911A-E085AA6D2533@microsoft.com...
> Insert the following worksheet event macro in the Sheet2 code area:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Set ws1 = Sheets("Sheet1")
> Set s1a1 = ws1.Range("A1")
> Set ws2 = Sheets("Sheet2")
> Set s2a2 = ws2.Range("A2")
> If Intersect(Target, s2a2) Is Nothing Then Exit Sub
> Application.EnableEvents = False
>    s1a1.Value = s1a1.Value - s2a2.Value
> Application.EnableEvents = True
> End Sub


Script is not working good..
When I enter in sheet2 number 3 I get number 2 in sheet1,and thats ok.
but when I delete number 3 in sheet2 and enter it again, I got number -1 in 
sheet1 , then enter again ,and I get -4 , and so on...
Where is the problem?
Thanks.


0
8/20/2008 1:28:47 PM
Sorry, it's working! :)
Thank you! 


0
8/20/2008 1:35:19 PM
The first time you run the script, it subtracts the 3 from the 5 and puts 2 
in the cell.
The second time you run the macro, it subtracts 3 again, so now the result 
is -1:

     5-3-3 => -1


The third time you run the macro, it subtracts 3 again, so now the result is 
-4:

    5-3-3-3 => -4
-- 
Gary''s Student - gsnu2007k


"KisH (Tihomir)" wrote:

> 
> "Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message 
> news:933DF882-BBB4-4FC5-911A-E085AA6D2533@microsoft.com...
> > Insert the following worksheet event macro in the Sheet2 code area:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Set ws1 = Sheets("Sheet1")
> > Set s1a1 = ws1.Range("A1")
> > Set ws2 = Sheets("Sheet2")
> > Set s2a2 = ws2.Range("A2")
> > If Intersect(Target, s2a2) Is Nothing Then Exit Sub
> > Application.EnableEvents = False
> >    s1a1.Value = s1a1.Value - s2a2.Value
> > Application.EnableEvents = True
> > End Sub
> 
> 
> Script is not working good..
> When I enter in sheet2 number 3 I get number 2 in sheet1,and thats ok.
> but when I delete number 3 in sheet2 and enter it again, I got number -1 in 
> sheet1 , then enter again ,and I get -4 , and so on...
> Where is the problem?
> Thanks.
> 
> 
> 
0
GarysStudent (1572)
8/20/2008 1:38:32 PM
Reply:

Similar Artilces:

Random Locked cells
I am having a problem with a spreadsheet that has random cells locked. The only way to unlock them is to copy and paste a blank cell that isn't locked into the cell that is locked, but this doesn't always work either Has anyone had this type of problem? Any ideas Thanks Leonar lmack@usccs.co Hi Leonard Maybe I don't understand you correct but if you select a cell or cells and right click on it and choose Format Cells you can change the locked on the Protection Tab -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Leonard Mack" <lmac...

selecting cells #4
When holding down ctrl to select a number of cells to format them or see the sum in the bottom of the screen - and you accidentally select one you dont want - is there a way to unselect that one without starting over ? Thanks, Yosef Take a look at this: http://www.cpearson.com/excel/unselect.htm there's no builtin way to do it, other than starting over... -- Regards, Juan Pablo Gonz´┐Żlez Excel MVP "ynissel" <ynissel@discussions.microsoft.com> wrote in message news:9EA0DDD8-4668-430B-BB5E-C15549FC9346@microsoft.com... > When holding down ctrl to select a number ...

format cell type
Hi everyone, in a spreadsheet, I have a complex data type that I want to sort correctly. The format contains a prefix that contains both letters(A) and numbers(0) followed by a suffix after a hyphen and another number. Example: 000A0000-1 and 000A0000-10 The thing Excel is messing up in, is if the complete prefix (000A0000) is the same, but a different number after the hyphen, it sorts it 1, 10, 11, 12, 13, ..., 2, 20, 21, 22, ... 3, etc. I was wondering if anyone knew how to set up a custom format type that will allow a suffix stored as text and the suffix after the hyphen stored as a n...

VLookUp and DropDown List in the same cell
Hi everyone, I know that this is possible but I'm not sure how to accomplish this. In column E, I have a list of zip code. In column I, I have the "Priority" category. Priority will be based on the zip code in column E. This will be accomplished by VLookUp formula, where I will have a separate sheet store all the zip codes and the priorities (ie. Priority #1, Priority #2 and so on). The challenge part is that column I "Priority" column needs to be able to use as a Drop Down List as well, in case that the VLookUp formula needs to be overridden for s...

Yodlee link broken again
Once again, access to my bank through MS Money ('07) via Yodlee is broken. This is getting tiresome. I haven't been able to update my account online in over a week. It claims that my "sign-in information" doesn't match the bank's records, when it does. I've verified this by logging into the online access of my bank via Internet Explorer with zero problems using the same username and password as configured in the account settings in Money. -- Jeffrey Kaplan www.gordol.org The from userid is killfiled Send pe...

Sorting cells #2
When you try to sort certain data by selecting the cells, but leaving adjacent cells unselected, you get a "sort warning" asking if you would like to expand the selection. How do you stop this from happening? Joey If you are selecting cells in one column or row only you will get this message. I have never found a way to prevent it. Hopefully we both can learn something here. Gord Dibben Excel MVP On Mon, 24 Jan 2005 13:39:05 -0800, "JoeyJoeJoe" <JoeyJoeJoe@discussions.microsoft.com> wrote: >When you try to sort certain data by selecting the cells, but lea...

Pivot Table Data Source, point to cell?
Is there any way to point the data source block to a cell. I want to simply update a cell (i.e. A1) with the range instead having to open the pivot table and changing from the wizard. Is there a way to do this? Example: Cell A1 contains: '[Output 3.24.xls]FORMALS-OUT'!$A$5:$P$65536 Data source in pivot table points to cell A1 to get the range. please tell me there's a way to do this.... thanx.! hi, Jeremy ! > Is there any way to point the data source block to a cell. > I want to simply update a cell (i.e. A1) with the range instead having to open the pivot table >...

Linking Charts to publisher file saved as web page
Good Day I hope someone can help me with this question - I am using publisher 2007 to create a newsletter ( Saved as web page ) on our local intarnet. I am now trying to add / link charts and excel tables to the newsletter. I am only able to paste as picture and have them dynamically update with daily sales for example. Can anyone help me with this . . . I am trying to find a way to insert excel charts and tables into my publisher 2007 webpage and have them automatically update when the excel files update - I do not want to copy and paste and re-publish everyday ...

I can't open any Emailed "LINKS" in Outlook 2003
Can anyone help me please dvs2000@bellsouth.net Diamond Dave <dvs2000@bellsouth.net> wrote: > Can anyone help me please What symptoms do you see? What error messages do you get? -- Brian Tillman ...

How ger a value of the one sheet based in a text in other sheet.
I have two excel workbooks with different data, but a column with the same information in them exists. As I obtain a value of one of books basing to me on the value thatexists in the common column? It sounds like =vlookup() or =index(match()) would work ok. Visit Debra Dalgleish's site: http://www.contextures.com/xlFunctions02.html for nice instructions. Carcastel wrote: > > I have two excel workbooks with different data, but a column with the same > information in them exists. As I obtain a value of one of books basing to me > on the value thatexists in the common column...

How do I display a text message when a cell has a certain value??
Hi, I have a cell where if the cell is empty (A1=0), then I would like the cell to display "Enter your value here." Otherwise, I would like the cell to display the value a person enters. I have tried using an if/then/else statement and, understandably, I get a circular reference error: =IF(A1=0,"Enter your value here",A1) Does anyone have a suggestion? Thanks! Why not just enter the text itself? The value entered by the user will displace (replace) it. This should work if the form is a template. -- HTH, RD ===================================================...

Counting Text Cells #2
Hi Guys, How can I determine the most common occurence of a word in a lis formatted as text in Column A. I would like a function that simpl returns the word that occurs the most to appear in cell D1. Also, i there are two words that occur the same number of times in this list, would like both words to be shown (one in D1, one in D2). Any suggestions? Thanks, Be -- Message posted from http://www.ExcelForum.com Hi to get the most frequent text entry try the following array formula (entered with CTRL+SHIFT+ENTER): =INDEX($B$1:$B$20,MODE(MATCH(IF($B$1:$B$20<>"",$B$1:$B$...

Why doesn't what I enter in the function bar appear in the cell?
I have a workbook that contains several copies of a master template worksheet. On one of these sheets when I enter text, it appears correctly in the function bar, but appears as a string of &s in the cell. This has only just occurred, and as far as I know used to work OK, i.e. it used to appear as text in the cell. What could cause this and what do I need to do to put it right? You wrote that the text appears as apersands (&). Did you really mean octothorpes (#)? If yes, then try formatting that cell as General--or anything but Text. Chris Mitchell wrote: > > I...

Conditional formatting
Hi Excel Experts I am creating several workbooks, one will be mine, and the others linked to mine and sent to people for them to revise and return to me to enable mine to automatically update, (the linked cells will contain blanks or numbers). Please could you tell me if there is a formula I could use in conditional formatting to enable me to identify in my workbook which linked cells will have changed, i.e. when a number is revised or added to blank cell the font colour changes , or if the number is deleted the blank cell changes colour? I hope I have explained myself correctly Man...

linking Excel table to word
Can anyone tell me how I can have an excel table linked to a word table so that the word table dynamically updates itself based on the values in excel cells? Much appreciate it if anyone can point me to direction Thrava Hi in MS Word just insert the Excel sheet as Excel object (Edit - Paste Special) -- Regards Frank Kabel Frankfurt, Germany "Thrava" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:0cea01c46e91$faae80b0$a501280a@phx.gbl... > Can anyone tell me how I can have an excel table linked to > a word table so that the word table dynamically u...

Adding text to a vlookup cell
Can a cell be edited if the contents are the result of a vlookup function and still keep the rseults? I have 2003 on an XP Pro PC. My formula is =IF(A4<>"",VLOOKUP(A4,InvDetail!$A$2:$B$3000,2,FALSE),"") I trying to create an availability list for our customers but need to add comments to the end of some items. I know I can add another column but the customers are used to the comment being after the item description and space on the page is limited. Thanks, Lee Coleman If you're talking about entering comments manually, then you'd need to concatenate th...

Custom Workspace Template Not Linking to Projects
Experts, I publised a test project on PWA for Project Server 2007. I then went to that new Workspace and made several changes to the Issues and Risks columns and over all views. I then saved the site as a template, sent the .stp file to our sharepoint admin and she executed the STSADM command to load new the template. I then went to PWA Site Provisioning setting and switched the default template to the new one. so far so good? When I went to create a new project schedule and published, i created the schedule and workspace but they're not linked. Meanining that I dont see t...

Formula to copy related information from sheet 1 to sheet 2
Hello guys, I hope you'll can help me. i have the information below in sheet 1. i need the information to be filtered, copy and paste in sheet 2 according to the month. example. if october, i need all information for the month of october to be copy and past from column A, B, C sheet 1 to column A, B, C sheet 2 please help. A B C Serial Number Quantities (Kilos) Date 8.1122 539,470 22/10/2009 8.1122 403,697 22/10/2009 8.1122 643,710 05/11/2009 8.1122...

Need help formatting a cell.
Can such a request be done using Excel? I need to enter a time in Cell B18 of 2325 or 11:25pm from that time i need it to calculate that cell B17 is 15 minutes prior to B18 (2310 or 11:10pm) B16 is 20 minutes prior to B18 (2305 or 11:05pm) B15 is 45 minutes prior to B18 (2240 or 10:40pm) B14 is 1 hour prior to B18 (2225 or 10:25pm) B13 is 1 hour 10 minutes prior to B18 (2215 or 10:15pm) and so one...... When i will need to change cell b18 to another time.... I need all my cells to still have the same minutes prior to be calculated and deducted. Sorry if questions is compliacted and conf...

Excel sheet size
Is it possible to increase the maximum number of rows beyond ~65536? Sorry, no. -- Andy. "Z" <Z@discussions.microsoft.com> wrote in message news:A494BD24-2A2D-48C8-A5CB-614BAEF2621E@microsoft.com... > Is it possible to increase the maximum number of rows beyond ~65536? No but there are ways to work around it "Z" <Z@discussions.microsoft.com> wrote in message news:A494BD24-2A2D-48C8-A5CB-614BAEF2621E@microsoft.com... : Is it possible to increase the maximum number of rows beyond ~65536? ...

Time formulae
Hi I use these formulae to insert the date and time in separate cells on a worksheet : A14 =TODAY() B14 =NOW()-TODAY() They update automatically when I open the sheet. It would be better however if they didn't do this until I use the Refresh All function. Is it possible to stop these functions updating until I need them to do so? I know I can switch global options off in the Options section of the program , but I'm loathe to do this for the sake of this one sheet. Grateful for any advice. ...

How do i select different cells to be use in a formula.
Hi I need to know how to select different cells to be used in a formula, but i don't know what is the separator that i need to use to this, see the example bellow Cells that i need to include on my formula: A1 C5 BH32 Thanks. Hi you didn't mention what formula but here's a starting point: =AVERAGE(A1,C5,BH32) or depending on your regional settings =AVERAGE(A1;C5;BH32) hope this helps Cheers JulieD "Manuel" <Manuel@discussions.microsoft.com> wrote in message news:A855A562-725C-4B10-B111-A88F7A767122@microsoft.com... > Hi > > I need to know ho...

How to store jpg in a database for use in another sheet #3
Can somebody please explain me how to store jpg's in a database and le them show in another sheet when entering a specific key -- Marc Willems ----------------------------------------------------------------------- Marc Willemse's Profile: http://www.msusenet.com/member.php?userid=164 View this thread: http://www.msusenet.com/t-187045633 ...

#REF Links
Why do I get a #REF error or #VALUE error with a spreadsheet tha calculates from another workbook? If I go to edit links and open th source document, the formulas "magically" fix themselves. Still, it' frustrating having to open the source file to fix -- rndyda ----------------------------------------------------------------------- rndydav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3533 View this thread: http://www.excelforum.com/showthread.php?threadid=55103 what formulas? Some, such as INDIRECT only work on OPEN workbooks. -- Don Guillet...

cell color #5
Dear All, My OS is WindowsXP pro and Office is 2003. In WindowsXP, there are many users, now there is one user in Excel, after apply the fill color, the color can not appear, only in Print Preview can see the color applied. help pls. thank you berry See this: http://support.microsoft.com/?id=320531 Biff "berrylei" <berrylei@discussions.microsoft.com> wrote in message news:7EE1354C-542D-4EDB-BDE6-52FDB6A3C78B@microsoft.com... > Dear All, > > My OS is WindowsXP pro and Office is 2003. > In WindowsXP, there are many users, now there is one user > in Excel...