A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Copy formula from one worksheet to another to get value in 2nd wks



 
 
Thread Tools Display Modes
  #1  
Old May 1st, 2010, 01:01 AM posted to microsoft.public.excel.worksheet.functions
JR
external usenet poster
 
Posts: 234
Default 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  
Old May 1st, 2010, 02:24 AM posted to microsoft.public.excel.worksheet.functions
PROF. GOVARDHAN JAYANTHI[_2_]
external usenet poster
 
Posts: 2
Default 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  
Old May 1st, 2010, 03:47 AM posted to microsoft.public.excel.worksheet.functions
Groybs
external usenet poster
 
Posts: 5
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:37 AM.


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