Referencing a Range of Sheets
 

I'm making a spreadsheet to keep track of some of my soccer team's
statistics throughout the season.

I'd like to present all the data on the first worksheet, and I plan to
make a new sheet for every match that we play. What I need is for
cells on the first sheet to reference the data from the cells on a
range of sheets.

My main requirement is along these lines: I need cell Squad!C2 to add
up and present the values cells Match1!C2 and Match2!C2 and Match3!C2
etc. I hope that makes sense, because once I have that solution the
rest of the spreadsheet follows along the same lines.

I don't have anywhere to upload the excel file, but I'd gladly e-mail
it to anyone who's willing to help.

Thanks in advance,
Graham Bae

Referencing a Range of Sheets
 

Try the following using a named range - info in help in excel

On the Insert menu, point to Name, and then click Define.
In the Names in workbook box, type the name. eg goals
If the Refers to box contains a reference delete it.
In the Refers to box, type = (equal sign).
Click the tab for the first worksheet to be referenced.
Hold down SHIFT and click the tab for the last worksheet to be referenced.
Select the cell or range of cells to be referenced.
Then in the cell where you want total to be type =SUM(goals)
--
John
MOS Master Instructor
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)


" wrote:

I'm making a spreadsheet to keep track of some of my soccer team's
statistics throughout the season.

I'd like to present all the data on the first worksheet, and I plan to
make a new sheet for every match that we play. What I need is for
cells on the first sheet to reference the data from the cells on a
range of sheets.

My main requirement is along these lines: I need cell Squad!C2 to add
up and present the values cells Match1!C2 and Match2!C2 and Match3!C2
etc. I hope that makes sense, because once I have that solution the
rest of the spreadsheet follows along the same lines.

I don't have anywhere to upload the excel file, but I'd gladly e-mail
it to anyone who's willing to help.

Thanks in advance,
Graham Bae

Referencing a Range of Sheets
 

Thanks John,

I tried your solution and it works a treat, but the only problem is
that I'd have to define a new name for each player's goals, assists,
yellow cards, red cards etc.

GrahamGoals, GrahamAssists, GrahamYellow, GrahamRed

That'd take a lot of work, so as lazy as it sounds, is there any way
that I can go about it with less effort?

Cheers!

Referencing a Range of Sheets
 

How about: In Squad!C2, enter =Sum(Match1:Match3!C2) and AutoFill down.

Abby

" wrote:

I'm making a spreadsheet to keep track of some of my soccer team's
statistics throughout the season.

I'd like to present all the data on the first worksheet, and I plan to
make a new sheet for every match that we play. What I need is for
cells on the first sheet to reference the data from the cells on a
range of sheets.

My main requirement is along these lines: I need cell Squad!C2 to add
up and present the values cells Match1!C2 and Match2!C2 and Match3!C2
etc. I hope that makes sense, because once I have that solution the
rest of the spreadsheet follows along the same lines.

I don't have anywhere to upload the excel file, but I'd gladly e-mail
it to anyone who's willing to help.

Thanks in advance,
Graham Bae