Paste-Link Problem

I have constructed a calculator in a spreadsheet that will calculate breach flow rate given values inputed by the user (Sheet 1). There is also a seperate spreadsheet within the same workbook that contains numourus different site numbers and their location (Sheet 2). I am wanting to include the calculated breach flow from sheet 1 into sheet 2 for each site. I have used the copy and paste link combo and it works for the first site but when I go and enter the data for the second site in the calculator it also changes my calculated value for the first site in sheet 2. Is there an IF statement that I need to include in sheet 2 to prohibiting the live update of the above cells? I hope that I explained this well enough. If need any more info let me know. Thanks in advance for your help

Shane Ic
shane.ice@ok.usda.gov
0
anonymous (74722)
6/7/2004 6:56:03 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
323 Views

Similar Articles

[PageSpeed] 16

I don't think you're going to be able to do this using formulas.

But you could have a macro that takes the values in Sheet1 (one at a time) and
populates sheet2 (the calculation engine).

After the calculation is done, then the value from a cell (or a bunch of cells)
is copied back to sheet1.

So you'd have your input cells in A:G (say) and your output cells in H:K (say).

You'd populate all the input cells (10000 rows!) and then run the macro to do
the calculation for each iteration.

This example takes 3 values (A:C of each row), puts them in 3 cells in sheet2,
calculates and returns two values to the sheet1.

Option Explicit
Sub testme()

    Dim InputWks As Worksheet
    Dim CalcWks As Worksheet
    Dim myRng As Range
    Dim myCell As Range
    
    Set InputWks = Worksheets("sheet1")
    Set CalcWks = Worksheets("sheet2")
   
    With InputWks
        'headers in row 1
        Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
    End With
       
    With CalcWks
        For Each myCell In myRng.Cells
            .Range("a1").Value = myCell.Value
            .Range("b3").Value = myCell.Offset(0, 1).Value
            .Range("c9").Value = myCell.Offset(0, 2).Value
            Application.Calculate
            myCell.Offset(0, 9).Value = .Range("g10").Value
            myCell.Offset(0, 10).Value = .Range("x99").Value
        Next myCell
    End With
    
End Sub


Shanei wrote:
> 
> I have constructed a calculator in a spreadsheet that will calculate breach flow rate given values inputed by the user (Sheet 1). There is also a seperate spreadsheet within the same workbook that contains numourus different site numbers and their location (Sheet 2). I am wanting to include the calculated breach flow from sheet 1 into sheet 2 for each site. I have used the copy and paste link combo and it works for the first site but when I go and enter the data for the second site in the calculator it also changes my calculated value for the first site in sheet 2. Is there an IF statement that I need to include in sheet 2 to prohibiting the live update of the above cells? I hope that I explained this well enough. If need any more info let me know. Thanks in advance for your help.
> 
> Shane Ice
> shane.ice@ok.usda.gov

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
6/8/2004 12:10:17 AM
Reply:

Similar Artilces:

XP Visual Style rendering problem
I am having a nasty problem with the "automatic" rendering of XP visual styles - I have an MFC-based app (a dialog-style "app" derived from CPropertySheet) to which I have added an XP manifest.. Almost everything paints just fine (but differently, of course), EXCEPT for when I "maximize" my app (my screen is running at 1152x864) - then, the bottom 10-15% of all my property page backgrounds is rendered with an almost white (*slight* blue tinge) versus the rest of the property page background, which is the "usual" XP Luna *slightly* yellow-brown dialog gr...

Pivot table chart problem
Hello! Can somebody help me to solve one insignificant problem related to pivot table charts. I want to have grand totals displayed in the chart, which was created using a pivot table. I tried many different ways, but couldn't succeed. Is it impossible? If so, are there any other ways to display the totals in the chart. I really apreciate your help. Thank you in advance. --- Message posted from http://www.ExcelForum.com/ I don't know of a way to include the grand total in a PivotChart. You can create a normal chart, based on the pivot table, and include the totals in that. On Jo...

Weird Calendering Problem
All, I've seen a lot of similar posts but have been having trouble regardless. We have Office XP on our Windows XP box, my wife and I each have our own XP login and our own .PST file for Outlook. If I send her a meeting request for an all day appointment, when it shows up in her calendar it shows up for 7pm->7pm the next day. In my calendar it's just all day, in hers it shows up starting at 7pm and spanning two days. The thing is, both of our time zones are set to Central Time GMT -6, and both of our daylight savings checkboxes are checked, and they are the same both in Outlook a...

HELP: problems using old version of Outlook....
Does anyone still use Outlook 97? I am having problems trying to setup an email account that uses POP/SMTP and also SPA for authentication for SMTP. POP/SMTP doesn't seem to be one of the available options in Outlook 97. The only email account types are MS Exchange, MS Mail, and something else but no SMTP or POP. Do I have to install some addon or plugins? It has to be brought up-to-date to be useful. Be sure you installed SR1 and SR2 and Internet E-mail Enhancement Patch (IMEP) for Outlook 97, then use the setup instructions for Outlook 98 found here: OL98: (CW) Configuring the ...

Unwanted "Calendar on behalf of" problem
To All, We're using Outlook 2007 and Exch 03 ENT. It's very common we all have some other users' shared calendars in the Outlook. However, some times by mistakes, users send out the invitations on behalf of one the "shared calendar's users" in Outlook instead of himself. After investigation, I found out if I click on one of the shared calendars, says John Doe's, then click on the "Calendar" bar in the navigation bar (left pane in Outlook), then I'm in John Doe's calendar instead of me. I can see the Outlook title has changed from "Cal...

Problem w. RUS in one subdomain
Hi, I have a problem with RUS. I have a forest with 5 domains. Root domain and four subdomains. Let's call them: top.tld a.top.tld b.top.tld c.top.tld d.top.tld Exchange 2003 server is installed in top.tld (exchange org consists of one server). RUS is created for each subdomain and domainprep has been run on each respective infrastructure master. a, b, and c domain users have exchange mailboxes and everything works well. However, "d" subdomain is having problems. Since RUS was created for that domain (today), I see two 8270 messages coming up on Exchange server and users ...

Having problems running powerpoint slide show!!!
Hi all, Could someone please assist me in regards to a problem i am having trouble running a power point slide presentation. The file is a large file approx 390mb i am running a fully up to date system XP intel pentium D 820 - 2GB memory - 512mb graphics - asus mother board. My PC is SP3. I running Microsoft Office 2003 but the file i am trying to run was done with 2007 so i installed the MS compatability pack. I know the file works and with sound as i viewed it on school computer with lower specs than mine. When i try run it takes from disc or HDD it takes approx 10 minutes...

How do I use Excel for an eigenvalue/vector problem?
Use the Poptools add in from http://www.cse.csiro.au/poptools/ -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "mr-ideahamster" <mr-ideahamster@discussions.microsoft.com> wrote in message news:E9A9213B-1AAB-41E2-8AB7-B7B3E93BDDB8@microsoft.com... > ...

Problem with outlook/windows
My computer has all of a sudden started running itself in safe mode and I get error messages whatever I try and do, all referring to a file called kbdg.dll. I can't find any reference to this file on the internet. Here is an example of a message I get when loading windows: OSA9 caused an invalid page fault in module KBDG.DLL at 015f:61c05319. Registers: EAX=0062eca4 CS=015f EIP=61c05319 EFLGS=00010202 EBX=00000000 SS=0167 ESP=0062ec48 EBP=0062ec68 ECX=00000004 DS=0167 ESI=00000078 FS=2307 EDX=00000001 ES=0167 EDI=ffffffff GS=0000 Bytes at CS:EIP: 66 83 39 00 8d 41 02 74 0a 66 8b 10 40 40 ...

Subform-Query Problems
I have read thru many other threads to try and find a solution to my problem but to no avail. My issue: I have two tables that I am trying to link so that my 2d table data can be used as a subform on the main form for which all the data on the 1st table is stored. What I am trying to do with the subform is to have a datasheet that shows the date of visit..type of 8823 issued..etc but to correspond to the PROJECT for which the visit is for in the Main Form. I get the message below.. I have tried to change my query and I have no idea what I am doing wrong. I have Project Name as my Primar...

XP & Outlook/Calendar Problem/New Virus-Not W.32 Blaster
I recently bought a new computer w/ XP. When I loaded Outlook 2002 and my records from another computer I found that the date on many of my all day free time appointments blended over to two days or more. The time zone shows the appointments are from 3PM to 3PM which causes this. New entries do not have this problem. When I try to remove the Outlook 2002 program and reinstall it the data base reappears like I never removed it. (This is without reloading the data base!) I have Norton's System Works software and have removed the the W32.Blaster Worm. According to this software I have...

Custom border problem #2
Having difficulty creating a custom border around a text box in Publisher 2000. After seleccting the graphic to use, we get the message that Publisher cannot open the file. It is not a question of file complexity or on a network as we see the same message no matter where the source. The account in question is a limited account, which we are assured, has managed to create custom borders in the past. I tried making a border in the admin account and it works OK which would suggest it is a question of permissions, but why should be so and why did it work once but no longer? TIA TWK Can ...

Links in email do not work
When I click on a link in an email I get the following message: "This operation has been cancelled due to restrictions in effect on this computer. Please contact your system administrator" Can anyone tell me what settings need to be changed. I am running Windows 7 and this popped up after having the computer shut down for the weekend. See the following http://www.slipstick.com/problems/link_restrict.htm -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "Motomo43&qu...

Problem with entering data in Pocket PC version of Money 2004
Dear all, When I synchronize my Pocket PC with my PC, all the data I had entered on the Pocket PC version of the Money 2004 disappeared on both PC and Pocket PC. Can anybody tell me what 's that problem and how to avoid. Thank you very much. -Kien Bui ...

Linking dates and values
I am trying to find the percent difference between two values. Eac value is the last work day of the month. My spreadsheet lists dates i column A and values in columns B-AI. How can I use the date to searc for the relative value? Thank -- seanbrow ----------------------------------------------------------------------- seanbrown's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2651 View this thread: http://www.excelforum.com/showthread.php?threadid=39782 On Mon, 22 Aug 2005 11:47:58 -0500, seanbrown <seanbrown.1u6duf_1124730421.4036@excelforum-nospam.com> ...

Problems with Exchange in a clustered configuration
We have Exchange configured in an active-passive configuration. We have problems failing over the resources to the passive node. I'll move the resources in clusadmin and it appears that the resources moved fine. About 10 minutes later, it'll try to move the resources back and half the resource group will be on one node, while the group with be on another. The only thing That seems to prevent the failover is that the "Exchange HTTP Virtual Server Instance" and some point fails. At least this is the only thing reported in the System log. Event ID 1069. Anyone see...

Problem with transactionnal replication
Hi I have a problem with replication on SQLServer 2000 SP4. This server is publisher and distributor. I have 2 publications: One merge One transactionnal They both are anonymous susbscriptions I have left the default SQLServer parameters : - For distributor : 72 hours for keeping the transations - For publications 15 days validation A snaphot for both publications is generated every night The connection and the replication is made on client through a VB6 application and the microsoft ActiveX for replication My problem is : For transactionnal replication 72 hours is to...

How do I copy and paste into an existing text box
When I copy text off of a web site and then try to paste into a text box that I am using in Publisher 2003, the copied text appears in a new text box, instead of inserting where I am typing. This new text box is also about 1/2" wid and 12" long or longer. I have to manipulate it to a normal shape before I can even begin to read what I've copied. I then have to do the copy and paste procedure all over again to get it into the text box I wanted to paste it in to begin with. I didn't have this problem with Publisher2000 and I can't figure out why Pub 2003 is doing...

http links in Outlook
Hi. When I get a mail in Outlook that contains a link to a website and I click the link, it opens the last accessed window I had opened in Internet Explorer. Is there any way to force the link to always open a new window? MN MN <MN@nospam.com> wrote: > When I get a mail in Outlook that contains a link to a website and I > click the link, it opens the last accessed window I had opened in > Internet Explorer. Is there any way to force the link to always open > a new window? Try this: In Internet Explorer, click Tools>Internet Options and click the Advanced tab. U...

Outlook 2003 outbox problem #3
I'm using OL2003 for POP mail to my ISP. If I have mail items sitting in my outbox waiting to be sent, and I click on Outbox in the folder list to view the items, then the font the Outlook uses to display the items' recipient and subject changes from italic to plain, the date shown changes to "None", and the items don't get sent when I do a send/receive. The only way to get them to be sent is to drag them to the Drafts folder, open each mail as if to edit it, click on the send button so that it goes to the Outbox, and then do a send/receive *without* looking at the Outbox...

problem using delphi dll in vc++
hi, I am using a delphi DLL in vc++,static linked with ".h"and "lib". the export functions in DLL are "__stdcall",but the function doesn't work well,it often returns some weird values. when I add codes as follows,it suddenly works well. why?? DWORD returnAdd; __asm { mov ecx,[ebp+4] mov returnAdd, ecx } I am confused,if there is something wrong with stack,I have already using "__stdcall",why it is still wrong? I don't even know what those asm code do,I just add them to get some ...

Contacts Problem #3
I posted once concerning this and was curious if anyone else has this dilemma. When first viewing my Contacts table in List View the last row is selected, which doesn't seem right. Then I click on the first row and then switch to Address Cards first contact is selected, seem right to me. When I switch back to List View the last row is selected. Is this really a behavior of Outlook??? It just doesn't seem right to me. Any help will be appreciated. Thanks, James ...

Problem in money when i update my brokerage
the program will not let me assign an investment from my brockergae account to microsoft money 2003 deluxe. Let me give an example: When I ask the program to download all transactions from my brokerage account to the program, it will do that without a problem. More specifically, when I am on a page in money called "Pick an account to use" I can see in my brokerage account I have "484 transactions to read". When I click "484 transactions to read" I am brought to another page called "Review investment activity." Here I can see all the transa...

contact linking
When I link a contact that contains both a first & last name and a company name, the first & last name is placed in the field. I have a user that would like the company name to be automatically placed in the field. Is this possible? We do notice that if the first/last name of a contact is not filled in and only the company name is, then the company name will drop in when linking the contact. -- cimarronmgt http://forums.slipstick.com No, its not possible unless you edit the full name field to include the company name. -- Diane Poremsky [MVP - Outlook] Ou...

Losing links in Favorites Folder
One of the users here is using Windows XP with Excel 2002. He always uses Favorites folders to store the files that he uses most. It has been fine until recent. Now, when he turns off the computer, whatever in his favorites folder will be gone. Does anyone have idea why? Thanks... ...