If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Copy formula from one worksheet to another to get value in 2nd wks
Hi, I have several worksheets where I have totaled certain columns. I want to
take that total and copy it onto a summary worksheet (not adding these formulas together) just need the value of the formula from worksheet B to show up on worksheet A and be able to change as totals on worksheet B change. I tried simply doing a copy and paste but I get a REF! error. I then tried to copy and paste special for formula and format and got same error. What do I do? Help! Example: Worksheet B has formula- =sum(f1:f22) which equal 395 Worksheet A needs the total of 395 to show up and be dynamic as total on worksheet B changes. |
#2
|
|||
|
|||
Copy formula from one worksheet to another to get value in 2nd wks
You just go to the summary worksheet; choose the cell where you want the
sum f1:f22 to appear; create a formula there to link to the cell containing the total in the first worksheet. To be more clear, Pul "=" in the cell where you want the total to appear; and enter the address of the target cell; (Tip: Put equal to in the target cell first and click on the cell where the total is already there.) Thanks; I hope you got it. "JR" wrote: Hi, I have several worksheets where I have totaled certain columns. I want to take that total and copy it onto a summary worksheet (not adding these formulas together) just need the value of the formula from worksheet B to show up on worksheet A and be able to change as totals on worksheet B change. I tried simply doing a copy and paste but I get a REF! error. I then tried to copy and paste special for formula and format and got same error. What do I do? Help! Example: Worksheet B has formula- =sum(f1:f22) which equal 395 Worksheet A needs the total of 395 to show up and be dynamic as total on worksheet B changes. |
#3
|
|||
|
|||
Copy formula from one worksheet to another to get value in 2nd wks
Prof.JAYANTHI is right, but here are a couple of things to look for to make
sure it's working right, and to look out for: 1. Open your source wksht and your summary wksht. 2. Place cursor at the location on the summary wksht where you want the cell value from the source wksht and begin by hitting either the = or + keys. The cell display will chage to "=| " where the cursor "|" will be blinking. 3. Select the Window pull down Menu [Alt+W] and choose the number of your source wksht from the list in the Menu. 4. The display will change to show the source wksht, but you will see a cell cursor marker that is a circulating dashed line. You can move that cell around the spreadsheet with either your mouse arrow keys. Place it on the cell that contains the value you want to export/link to your summary wksht. 5. Hit ENTER and your view will change back to your summary wksht with the value from your source entered into the cell you originally chose. If you need to sum cells from different sheets or multiple cells from the same source wksht, just hit the + key instead of ENTER and repeat the process until your done, then hit ENTER. 6: THINGS TO LOOK OUT FOR - A) In the cell in your summary wksht where you placed the imported value from source, you will see a FORMULA notation that looks like this: =+'F:\LATIN_Id.xls'!Latin1 - The name of your source wksht is contained within the two apostrophes; - The ! point needs to be there to mark the cell number - The cell containing your source value/data immediately follows the ! mark. It will either show as a cell address (e.g. E5), or as a range name if you named it (e.g. Latin1). - If you are linking multiple cells from one source document into your summary wksht, then name each of the value cells in your source wksht as something you can remember. Then when you go to load your summary wksht, hit the = key and navigate to the wksht and cell location and hit ENTER for the first link. Then simply copy that cell content to as many target cells as you have source cell information. Edit each of those copies by simply replacing the RANGE name that was copied in the formula with a new range name. The summary sheet will reflect the new value from the different source cell. NOTE: You can move the source wksht around your whole network, and it will not affect the content of your summary sheet, nor will it generate an error. However, the drive location will change automatically in your cell formula. LOOK OUT: If you happen to be running two sessions of Excel simultaneously (i.e. you're on a network with multiple licenses and youve triggered two desktop icons) then you will not be able to link to a cell in a worksheet that is open in a different session window. Other than that, this should work. "JR" wrote: Hi, I have several worksheets where I have totaled certain columns. I want to take that total and copy it onto a summary worksheet (not adding these formulas together) just need the value of the formula from worksheet B to show up on worksheet A and be able to change as totals on worksheet B change. I tried simply doing a copy and paste but I get a REF! error. I then tried to copy and paste special for formula and format and got same error. What do I do? Help! Example: Worksheet B has formula- =sum(f1:f22) which equal 395 Worksheet A needs the total of 395 to show up and be dynamic as total on worksheet B changes. |
Thread Tools | |
Display Modes | |
|
|