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

[Guest]

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!

 

[Guest]

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.

 

[Guest]

Right click or left click?
Thanks!!

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.

 

[Guest]

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.

 

[Guest]

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

Susan

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

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!

 

[emgee100]

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.

 

[emgee100]

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!

 

[Gord Dibben]

In a cell on your Summary sheet enter =SUM(Sheet1:Sheet10!G24)

That will give you total of G24 from all sheets.

If the totals are in different cells on each sheet you will have to compound by
entering the = sign in a cell on Summary sheet then switch to sheet1 and
select a cell, then hit + sign, switch to sheet2 and select a cell. hit + sign

Follow this pattern untill you run out of sheets to switch to then hit ENTER
key.

Example formula =Sheet1!G12 + Sheet2!H34 + Sheet3!B23


Gord Dibben MS Excel MVP

 

[larc]

I need to transpose cell values (sum) of columns on three spreedsheets so
that the information appears in a single column on the summary sheet within
the same workbook. I have used combinations of = and + signs with $ but with
no luck. Is there a simple command I can use to ahieve the desired result and
allow for subsequent changes to the information on the original spreedsheets?

 

[Spiky]

I need to transpose cell values (sum) of columns on three spreedsheets so
that the information appears in a single column on the summary sheet within
the same workbook. I have used combinations of = and + signs with $ but with
no luck. Is there a simple command I can use to ahieve the desired result and
allow for subsequent changes to the information on the original spreedsheets?

You should start your own thread. Also, transpose means to move from
columns to rows. I don't think that is what you want to do.

Are you trying to just get a SUM of the columns, or are you trying to
move all the data from each to show in the summary sheet?

 

[nso]

i have a workbook with several spreadsheets. in each row on each spreadsheet
there is a sku, upc, description, empty cell to enter floor count (we'll call
this A), empty cell to enter backroom count (we'll call this B), empty cell
to enter system count (we'll call this C), cell that automatically calculates
the difference between the system count and the combined totals of floor and
backroom counts (D= C-(A+B)).

what i need it to do is- if D is more or less than 0, i want that whole ROW
copied to a blank spreadsheet at the end of my workbook. so far, i have only
been able to get it to copy the first cell from that row, but i need the
whole thing. is there a way to do this other than putting a formula in each
row in that column??? thanks, i hope i explained this well.

 

[Bill]

I'm new, so might do some things wrong; just let me know if I do. MY problem:
I work for a non-profit organization that uses a lot of volunteers. The
organization wishes to keep up with all hours of each volunteer with a total
for each week, month, year for each volunteer. I've created a spreadsheet
with 5 worksheets, one for each week of the month and a 6th sheet for monthly
totals for individuals. The first sheet has columns:
A=volunteer ID
B=Volunteer name
C=date
D=time in
E=time out
F=total time
G=volunteer function
G=time in
I=time out
J=total time
K=volunteer function
L=total time (adding F and J)

The monthly sheet has:
A=volunteer ID
B=volunteer name
C=total of first week (to be carried forward from "L" )
D=total of second week
E=total of third week
F=total of fourth week
G=toatl of fifth week (when one exists)
H=sum of all weeks for the month

A part of the problem is that each volunteer comes in, signs in on the next
available row in the sheet for that week, using his/her ID num and name,
which will not be in numerical order, but needs to be carried forward to the
monthly sheet to the right name for calculating time.
Can this be done? If so, how?
Thanks in advance.
Bill

 

[nicole]

Hi David, I hope you're still checking this forum (since your post is from 3
years ago) but I was hoping you could tell me how to do the cell linking
between 2 separate xls files with entire columns.
I have a column in spreadsheet 1 with Yes/No values (200 plus cells). Do I
need to do the cell reference for each cell or is there a way to set up the
reference between spreadsheets for an entire column? I hope there is instead
of me having to manually reference hundreds of cells. Any advice you could
give would be greatly appreciated.

 

[Gord Dibben]

Select a column or columns in workbook 1 and Copy

Select a cell in Workbook 2 and Paste Special>Paste Links>OK>Esc

You may not need entire columns..........you can select ranges to copy and
paste as above.


Gord Dibben MS Excel MVP

 

[LL]

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

 

[LL]

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

 

[Gord Dibben]

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