Help!

How do I link cells from one excel worksheet to another?

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Worksheet Functions RSS
Next:  Replace nth place with colon  
Author Message
stb
External


Since: Oct 05, 2005
Posts: 4



PostPosted: Wed Oct 05, 2005 4:08 pm    Post subject: How do I link cells from one excel worksheet to another?
Archived from groups: microsoft>public>excel>worksheet>functions (more info?)

I want to link the totals of certain columns of several worksheets to a
summary worksheet in the same workbook. I keep seperate worksheets of the
circulation stats of my library branches, and I want the totals of my columns
to automatically show up on the cumulative worksheet as I enter new circ
figures for each month in the year. Everything I've looked up talks about
linking to different types of Office programs, not something as simple (but
apparently hard to do) as this. Any help greatly appreciated!
Back to top
David Billigmeier
External


Since: Sep 12, 2005
Posts: 331



PostPosted: Wed Oct 05, 2005 4:46 pm    Post subject: RE: How do I link cells from one excel worksheet to another? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Type an equals sign in the cell you want the information to end up in, click
over to another worksheet, and click on the cell you want to reference.
Excel will automatically enter it in.

--
Regards,
Dave


"stb" wrote:

> I want to link the totals of certain columns of several worksheets to a
> summary worksheet in the same workbook. I keep seperate worksheets of the
> circulation stats of my library branches, and I want the totals of my columns
> to automatically show up on the cumulative worksheet as I enter new circ
> figures for each month in the year. Everything I've looked up talks about
> linking to different types of Office programs, not something as simple (but
> apparently hard to do) as this. Any help greatly appreciated!
Back to top
stb
External


Since: Oct 05, 2005
Posts: 4



PostPosted: Wed Oct 05, 2005 5:16 pm    Post subject: RE: How do I link cells from one excel worksheet to another? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Right click or left click?
Thanks!!

"David Billigmeier" wrote:

> Type an equals sign in the cell you want the information to end up in, click
> over to another worksheet, and click on the cell you want to reference.
> Excel will automatically enter it in.
>
> --
> Regards,
> Dave
>
>
> "stb" wrote:
>
> > I want to link the totals of certain columns of several worksheets to a
> > summary worksheet in the same workbook. I keep seperate worksheets of the
> > circulation stats of my library branches, and I want the totals of my columns
> > to automatically show up on the cumulative worksheet as I enter new circ
> > figures for each month in the year. Everything I've looked up talks about
> > linking to different types of Office programs, not something as simple (but
> > apparently hard to do) as this. Any help greatly appreciated!
Back to top
David Billigmeier
External


Since: Sep 12, 2005
Posts: 331



PostPosted: Wed Oct 05, 2005 5:26 pm    Post subject: RE: How do I link cells from one excel worksheet to another? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Left click, just like you are switching over to view the other spreadsheet.
You will see excel will add (for example say your second sheet is named
Sheet2) Sheet2! preceeding any cell references.


--
Regards,
Dave


"stb" wrote:

> Right click or left click?
> Thanks!!
>
> "David Billigmeier" wrote:
>
> > Type an equals sign in the cell you want the information to end up in, click
> > over to another worksheet, and click on the cell you want to reference.
> > Excel will automatically enter it in.
> >
> > --
> > Regards,
> > Dave
> >
> >
> > "stb" wrote:
> >
> > > I want to link the totals of certain columns of several worksheets to a
> > > summary worksheet in the same workbook. I keep seperate worksheets of the
> > > circulation stats of my library branches, and I want the totals of my columns
> > > to automatically show up on the cumulative worksheet as I enter new circ
> > > figures for each month in the year. Everything I've looked up talks about
> > > linking to different types of Office programs, not something as simple (but
> > > apparently hard to do) as this. Any help greatly appreciated!
Back to top
stb
External


Since: Oct 05, 2005
Posts: 4



PostPosted: Wed Oct 05, 2005 5:32 pm    Post subject: RE: How do I link cells from one excel worksheet to another? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

David,
Just tried it and it works like a charm. Just what I was looking for!
Thanks a million!!

Susan

"David Billigmeier" wrote:

> Left click, just like you are switching over to view the other spreadsheet.
> You will see excel will add (for example say your second sheet is named
> Sheet2) Sheet2! preceeding any cell references.
>
>
> --
> Regards,
> Dave
>
>
> "stb" wrote:
>
> > Right click or left click?
> > Thanks!!
> >
> > "David Billigmeier" wrote:
> >
> > > Type an equals sign in the cell you want the information to end up in, click
> > > over to another worksheet, and click on the cell you want to reference.
> > > Excel will automatically enter it in.
> > >
> > > --
> > > Regards,
> > > Dave
> > >
> > >
> > > "stb" wrote:
> > >
> > > > I want to link the totals of certain columns of several worksheets to a
> > > > summary worksheet in the same workbook. I keep seperate worksheets of the
> > > > circulation stats of my library branches, and I want the totals of my columns
> > > > to automatically show up on the cumulative worksheet as I enter new circ
> > > > figures for each month in the year. Everything I've looked up talks about
> > > > linking to different types of Office programs, not something as simple (but
> > > > apparently hard to do) as this. Any help greatly appreciated!
Back to top
K Smith
External


Since: Feb 10, 2006
Posts: 1



PostPosted: Fri Feb 10, 2006 6:21 pm    Post subject: How do I link cells from several worksheets? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

I have a workbook with 6 worksheets and I want a total page that will total
the same cells from each sheet, say A8, B9, etc
I've tried +,+,+, but got errors.
Suggestions?
Back to top
stb
External


Since: Oct 05, 2005
Posts: 4



PostPosted: Fri Feb 10, 2006 6:35 pm    Post subject: RE: How do I link cells from several worksheets? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

"K Smith" wrote:

> I have a workbook with 6 worksheets and I want a total page that will total
> the same cells from each sheet, say A8, B9, etc
> I've tried +,+,+, but got errors.
> Suggestions?

David's reply using the = sign instead of + worked for me. I've got 6
branches with circulation stats that I link to a totals page, and while I did
it for a range of cells, I'm sure it would work for a single cell also.
Susan
Back to top
Bianca
External


Since: Sep 25, 2004
Posts: 6



PostPosted: Wed May 31, 2006 10:26 am    Post subject: RE: How do I link cells from one excel worksheet to another? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

I have created 1 worksheet with lots of information in different columns. Now
I would like to create a new overview in a new worksheet that shows only the
information from 3 of the columns from worksheet 1. It would be great if
worksheet 1 always updates the information in the other worksheets. The
question here is how do I link the colums without enlarging my file big time?

I tried to do so with the equals sign, with paste special and paste links
but that wasn't the best way to do so.....any suggestions?

Thanks a lot!

"David Billigmeier" wrote:

> Left click, just like you are switching over to view the other spreadsheet.
> You will see excel will add (for example say your second sheet is named
> Sheet2) Sheet2! preceeding any cell references.
>
>
> --
> Regards,
> Dave
>
>
> "stb" wrote:
>
> > Right click or left click?
> > Thanks!!
> >
> > "David Billigmeier" wrote:
> >
> > > Type an equals sign in the cell you want the information to end up in, click
> > > over to another worksheet, and click on the cell you want to reference.
> > > Excel will automatically enter it in.
> > >
> > > --
> > > Regards,
> > > Dave
> > >
> > >
> > > "stb" wrote:
> > >
> > > > I want to link the totals of certain columns of several worksheets to a
> > > > summary worksheet in the same workbook. I keep seperate worksheets of the
> > > > circulation stats of my library branches, and I want the totals of my columns
> > > > to automatically show up on the cumulative worksheet as I enter new circ
> > > > figures for each month in the year. Everything I've looked up talks about
> > > > linking to different types of Office programs, not something as simple (but
> > > > apparently hard to do) as this. Any help greatly appreciated!
Back to top
LL
External


Since: May 22, 2006
Posts: 14



PostPosted: Mon Aug 31, 2009 6:16 pm    Post subject: RE: How do I link cells from one excel worksheet to another? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Does the linking process have to contain numbers and formulas or can it
contain words and symbols. I want to be able to update e-mail addresses in
one location and have the change cross over to other sheets in the same
workbook. thanks LL

"David Billigmeier" wrote:

> Type an equals sign in the cell you want the information to end up in, click
> over to another worksheet, and click on the cell you want to reference.
> Excel will automatically enter it in.
>
> --
> Regards,
> Dave
>
>
> "stb" wrote:
>
> > I want to link the totals of certain columns of several worksheets to a
> > summary worksheet in the same workbook. I keep seperate worksheets of the
> > circulation stats of my library branches, and I want the totals of my columns
> > to automatically show up on the cumulative worksheet as I enter new circ
> > figures for each month in the year. Everything I've looked up talks about
> > linking to different types of Office programs, not something as simple (but
> > apparently hard to do) as this. Any help greatly appreciated!
Back to top
LL
External


Since: May 22, 2006
Posts: 14



PostPosted: Mon Aug 31, 2009 6:23 pm    Post subject: RE: How do I link cells from one excel worksheet to another? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

David - I have done this and I get a "0" in the cell. Not sure why the cell
is not transferring. It shows me the equal sign followed by the name of the
sheet followed by and ! followed by the cell designation on the sheet where I
want to transfer the information but the end result is a "0". thanks - LL

"LL" wrote:

> Does the linking process have to contain numbers and formulas or can it
> contain words and symbols. I want to be able to update e-mail addresses in
> one location and have the change cross over to other sheets in the same
> workbook. thanks LL
>
> "David Billigmeier" wrote:
>
> > Type an equals sign in the cell you want the information to end up in, click
> > over to another worksheet, and click on the cell you want to reference.
> > Excel will automatically enter it in.
> >
> > --
> > Regards,
> > Dave
> >
> >
> > "stb" wrote:
> >
> > > I want to link the totals of certain columns of several worksheets to a
> > > summary worksheet in the same workbook. I keep seperate worksheets of the
> > > circulation stats of my library branches, and I want the totals of my columns
> > > to automatically show up on the cumulative worksheet as I enter new circ
> > > figures for each month in the year. Everything I've looked up talks about
> > > linking to different types of Office programs, not something as simple (but
> > > apparently hard to do) as this. Any help greatly appreciated!
Back to top
Gord Dibben
External


Since: Feb 23, 2004
Posts: 9431



PostPosted: Mon Aug 31, 2009 7:31 pm    Post subject: Re: How do I link cells from one excel worksheet to another? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

If there is no data in the source cell a zero will display when

=Sheet1!A1 is used.

To trap for this

=IF(Sheet1!A1="","",Sheet1!A1)


Gord Dibben MS Excel MVP

On Mon, 31 Aug 2009 14:23:02 -0700, LL wrote:

>David - I have done this and I get a "0" in the cell. Not sure why the cell
>is not transferring. It shows me the equal sign followed by the name of the
>sheet followed by and ! followed by the cell designation on the sheet where I
>want to transfer the information but the end result is a "0". thanks - LL
>
>"LL" wrote:
>
>> Does the linking process have to contain numbers and formulas or can it
>> contain words and symbols. I want to be able to update e-mail addresses in
>> one location and have the change cross over to other sheets in the same
>> workbook. thanks LL
>>
>> "David Billigmeier" wrote:
>>
>> > Type an equals sign in the cell you want the information to end up in, click
>> > over to another worksheet, and click on the cell you want to reference.
>> > Excel will automatically enter it in.
>> >
>> > --
>> > Regards,
>> > Dave
>> >
>> >
>> > "stb" wrote:
>> >
>> > > I want to link the totals of certain columns of several worksheets to a
>> > > summary worksheet in the same workbook. I keep seperate worksheets of the
>> > > circulation stats of my library branches, and I want the totals of my columns
>> > > to automatically show up on the cumulative worksheet as I enter new circ
>> > > figures for each month in the year. Everything I've looked up talks about
>> > > linking to different types of Office programs, not something as simple (but
>> > > apparently hard to do) as this. Any help greatly appreciated!
Back to top
Diamondlite
External


Since: Oct 21, 2009
Posts: 1



PostPosted: Wed Oct 21, 2009 9:25 pm    Post subject: RE: How do I link cells from one excel worksheet to another? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

If you want to copy a cell entry from one worksheet into another worksheet
and have that 2nd worksheet change automatically when you change the data in
the cell in the first worksheet , here's how:
1) In the first worksheet, right click in the cell you want to copy and
click "copy"
2) Go to the cell in the 2nd worksheet that you want to enter the same
data,right click in that cell and click "paste"
3) After you paste it, a little box will appear on the bottom right of the
cell (looks like a folder/envelope). Click on that and a dropdown box will
appear. Click on "Link cells" if the cell you're copying doesn't have a
formula associated with it, or click on "Values and number formatting" if the
cell your copying has a formula in it (or click on any other item that
applies)
4) You're done. Now if you change the value in the original worksheet, it
will change in the 2nd worksheet automatically..


"stb" wrote:

> I want to link the totals of certain columns of several worksheets to a
> summary worksheet in the same workbook. I keep seperate worksheets of the
> circulation stats of my library branches, and I want the totals of my columns
> to automatically show up on the cumulative worksheet as I enter new circ
> figures for each month in the year. Everything I've looked up talks about
> linking to different types of Office programs, not something as simple (but
> apparently hard to do) as this. Any help greatly appreciated!
Back to top
Jerry
External


Since: Apr 22, 2006
Posts: 130



PostPosted: Wed Nov 18, 2009 10:39 am    Post subject: RE: How do I link cells from one excel FILE TO another? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

How do you / or is there a way to link cells from one Excell file to another
file? I have a large file with lots of data on a spreadsheet and I was
wanting to create a new file that would be a quick look at a summary from the
large file. Thanks
Back to top
mfk
External


Since: Nov 19, 2009
Posts: 1



PostPosted: Thu Nov 19, 2009 4:44 pm    Post subject: RE: How do I link cells from one excel worksheet to another? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

I had to "enter" to make the information show on target sheet in workbook!

"David Billigmeier" wrote:

> Type an equals sign in the cell you want the information to end up in, click
> over to another worksheet, and click on the cell you want to reference.
> Excel will automatically enter it in.
>
> --
> Regards,
> Dave
>
>
> "stb" wrote:
>
> > I want to link the totals of certain columns of several worksheets to a
> > summary worksheet in the same workbook. I keep seperate worksheets of the
> > circulation stats of my library branches, and I want the totals of my columns
> > to automatically show up on the cumulative worksheet as I enter new circ
> > figures for each month in the year. Everything I've looked up talks about
> > linking to different types of Office programs, not something as simple (but
> > apparently hard to do) as this. Any help greatly appreciated!
Back to top
etk
External


Since: Jan 26, 2010
Posts: 1



PostPosted: Tue Jan 26, 2010 8:42 pm    Post subject: RE: How do I link cells from one excel worksheet to another? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

"stb" wrote:

> I want to link the totals of certain columns of several worksheets to a
> summary worksheet in the same workbook....

Here is a slightly more complicated version of this question (Excel 2007):

I have many copies of the same .xlsx worksheet with different text values on
Sheet1 of the form, and I anticipate getting many more. I would like to
create a summary sheet for all of these forms by specifying the NAME of the
..xlsx file in a cell and populate the cell information from specific cells
out of that file for each row in the summary worksheet. I thought that
perhaps I could specify the cell in the formula bar to dynamically update
based on the name of the file such as this:

=[D2]Sheet1!$B$6

where cell D2 in the destination workbook contains the file name text I
would like to pull from. As I add new files to my list I could then just add
the file name to the bottom of the list (in column "D") and the relevant info
would be populated in the other columns of the workbook...

Apparently this approach doesn't work due to the way the links are defined.
Is there any way to dynamically setup a link to files so I can just add the
file name to the workbook and have it pull out the needed info?

thx!
Back to top
David Biddulph
External


Since: Feb 24, 2007
Posts: 1373



PostPosted: Wed Jan 27, 2010 7:10 am    Post subject: Re: How do I link cells from one excel worksheet to another? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Look at the INDIRECT function. Details in Excel help.
--
David Biddulph

"etk" wrote in message

>
> "stb" wrote:
>
>> I want to link the totals of certain columns of several worksheets to a
>> summary worksheet in the same workbook....
>
> Here is a slightly more complicated version of this question (Excel 2007):
>
> I have many copies of the same .xlsx worksheet with different text values
> on
> Sheet1 of the form, and I anticipate getting many more. I would like to
> create a summary sheet for all of these forms by specifying the NAME of
> the
> .xlsx file in a cell and populate the cell information from specific cells
> out of that file for each row in the summary worksheet. I thought that
> perhaps I could specify the cell in the formula bar to dynamically update
> based on the name of the file such as this:
>
> =[D2]Sheet1!$B$6
>
> where cell D2 in the destination workbook contains the file name text I
> would like to pull from. As I add new files to my list I could then just
> add
> the file name to the bottom of the list (in column "D") and the relevant
> info
> would be populated in the other columns of the workbook...
>
> Apparently this approach doesn't work due to the way the links are
> defined.
> Is there any way to dynamically setup a link to files so I can just add
> the
> file name to the workbook and have it pull out the needed info?
>
> thx!
>
>
>
>
Back to top
abdul
External


Since: Feb 24, 2010
Posts: 4



PostPosted: Wed Feb 24, 2010 1:01 pm    Post subject: RE: How do I link cells from one excel worksheet to another? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

"stb" wrote:

> I want to link the totals of certain columns of several worksheets to a
> summary worksheet in the same workbook. I keep seperate worksheets of the
> circulation stats of my library branches, and I want the totals of my columns
> to automatically show up on the cumulative worksheet as I enter new circ
> figures for each month in the year. Everything I've looked up talks about
> linking to different types of Office programs, not something as simple (but
> apparently hard to do) as this. Any help greatly appreciated!
Back to top
Luke M
External


Since: Feb 24, 2010
Posts: 60



PostPosted: Wed Feb 24, 2010 4:37 pm    Post subject: Re: How do I link cells from one excel worksheet to another? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Start typing a formula by pressing "=", then navigate to the cell you want
to link to.

Or, you might be able to use a 3D reference using the SUM formula (see XL
help on 3d references for more detail)

--
Best Regards,

Luke M
"abdul" wrote in message

>
>
> "stb" wrote:
>
>> I want to link the totals of certain columns of several worksheets to a
>> summary worksheet in the same workbook. I keep seperate worksheets of
>> the
>> circulation stats of my library branches, and I want the totals of my
>> columns
>> to automatically show up on the cumulative worksheet as I enter new circ
>> figures for each month in the year. Everything I've looked up talks
>> about
>> linking to different types of Office programs, not something as simple
>> (but
>> apparently hard to do) as this. Any help greatly appreciated!
Back to top
Buckeroo
External


Since: Mar 01, 2010
Posts: 1



PostPosted: Mon Mar 01, 2010 7:16 pm    Post subject: RE: How do I link cells from one excel worksheet to another? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

"emgee100" wrote:

> Typing equals, clicking on Sheet 1 and clicking on Sheet 2 isn't working for
> me. I get a '0' in the cell, rather than the text from Sheet 1. Seems like
> the cell should be formatted differently, but I've tried all the options.
> Help!
>
> "David Billigmeier" wrote:
>
> > Type an equals sign in the cell you want the information to end up in, click
> > over to another worksheet, and click on the cell you want to reference.
> > Excel will automatically enter it in.
> >
> > --
> > Regards,
> > Dave
> >
> >
> > "stb" wrote:
> >
> > > I want to link the totals of certain columns of several worksheets to a
> > > summary worksheet in the same workbook. I keep seperate worksheets of the
> > > circulation stats of my library branches, and I want the totals of my columns
> > > to automatically show up on the cumulative worksheet as I enter new circ
> > > figures for each month in the year. Everything I've looked up talks about
> > > linking to different types of Office programs, not something as simple (but
> > > apparently hard to do) as this. Any help greatly appreciated!
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Worksheet Functions All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum