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.

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.

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.

1. Open the spread sheet where you need the value of the formula

2. Enter "=" in the cell where you want the result.

3. Click on the Cell in the worksheet whose value is required.

(NOTE: Both files need to be open simultaneously if the transmission is across 2 files.)

On Friday, April 30, 2010 8:01 PM 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.


On Friday, April 30, 2010 9:24 PM PROF. GOVARDHAN JAYANTHI wrote:

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:


On Friday, April 30, 2010 10:47 PM Groybs wrote:

Prof.JAYANTHI is right, but here are a couple of things to look for to make
sure it is 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 are 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:


On Sunday, March 20, 2011 9:00 AM Pawan Teza wrote:

Everybody who gave their opinions.., really i appreciate your talent in excel. But let me tell you one thing people, I am a student of Prof. Govardhan. I think he just asked this question to know how many of you know the answer. He is such a brilliant person and so i don't think he doesn't know the answer.