Go Back   Tech Support Forum > Microsoft Support > Microsoft Office support

Excel formula over multiple sheets

This is a discussion on Excel formula over multiple sheets within the Microsoft Office support forums, part of the Tech Support Forum category. Hi, I want to make formula in excel but the data is spread over multiple sheets, how can that be


Reply
 
Thread Tools Search this Thread
Old 12-07-2005, 07:02 AM   #1
Registered Member
 
Join Date: Dec 2005
Posts: 21
OS: XP



Hi,

I want to make formula in excel but the data is spread over multiple sheets, how can that be accomplished?

Thanks,

Just me.

__________________
spxtechsupport is offline   Reply With Quote
Old 12-07-2005, 01:16 PM   #2
Team Manager, Articles
Analyst
Rangemaster, TSF Academy
 
Glaswegian's Avatar
 
Join Date: Sep 2005
Location: Glasgow
Posts: 38,483
OS: Win XP Pro SP3 / Win 7 Pro

My System

Hi and welcome to TSF.

You would have to reference the other sheets and cells. So if I wanted to sum cell A1 on sheets 2 and 3, and put the answer in sheet 1, my formula would look something like this

=SUM(Sheet2!A1,Sheet3!A1)

If you can provide some more info, then we can provide a more detailed answer.

Regards

__________________
Iain - Defender of the Haggis and all things Scottish.
I don't help by PM - post in the Forums.



PC Safety & Security::PC running a bit slow?::Photographers Corner
Glaswegian is offline   Reply With Quote
Old 12-07-2005, 01:28 PM   #3
Henry
 
Spatcher's Avatar
 
Join Date: Apr 2005
Posts: 3,485
OS: Mac OSX


Yeah Glasweigan said it but my way is better IMO.

To make it easier, say you go to your end sheet, type
=*then your function*
and choose the first sheet and choose cell, say, B1. Then you press and hold shift, and press your end sheet. It should end up looking like (using AVERAGE)

EX: =AVERAGE(Worksheet1:Worksheet5!b1)

If you didn't follow any of that, use my example function instead of Glaswegians

EDIT: But my way is intended for using a function for just one cell across multiple sheets.
__________________
JohnThePilot: Cenedl heb iaith, cenedl heb galon (a nation without a language is a nation without a heart)
John Butler Trio: Go take a step outside, see what's shakin' in the real world - Good Excuse
Paul Kelly: Just add a dollop of tomato sauce for sweetness and that extra tang - How to Make Gravy
Spatcher is offline   Reply With Quote
Old 12-07-2005, 02:30 PM   #4
Team Manager, Articles
Analyst
Rangemaster, TSF Academy
 
Glaswegian's Avatar
 
Join Date: Sep 2005
Location: Glasgow
Posts: 38,483
OS: Win XP Pro SP3 / Win 7 Pro

My System

Quote:
Originally Posted by HenryVI
Yeah Glasweigan said it but my way is better IMO.

To make it easier, say you go to your end sheet, type
=*then your function*
and choose the first sheet and choose cell, say, B1. Then you press and hold shift, and press your end sheet. It should end up looking like (using AVERAGE)

EX: =AVERAGE(Worksheet1:Worksheet5!b1)

If you didn't follow any of that, use my example function instead of Glaswegians

EDIT: But my way is intended for using a function for just one cell across multiple sheets.
You following me Henry?

As I said, I think we need some more info here.
__________________
Iain - Defender of the Haggis and all things Scottish.
I don't help by PM - post in the Forums.



PC Safety & Security::PC running a bit slow?::Photographers Corner
Glaswegian is offline   Reply With Quote
Old 05-16-2008, 02:46 PM   #5
Registered Member
 
Join Date: May 2008
Posts: 1
OS: Mac OS X



Hi,
Sorry to hijack this thread but I didn't want to start a new one.

I'm searching for a formula that will calculate the average grade over multiple sheets.
I'm a music teacher and I want to calculate the average mark for each element of my courses.
Sheet 1 contains all the information for my Scottish Music Unit (elements = Instrument 1, Instrument 2, Listening and Composing)
Sheet 2 - Jazz Unit
Sheet 3 - World Music
etc.

In each Unit there are 4 pieces of music - they are tested each week on each instrument and given a score /5.
I have a list of the pupils names running down Column A - 1 to 20.
So for each piece I have labelled the columns - Instr1 Instr2 Instr1 Instr2 . . . Composing Listening

So on the final Sheet - I would like to have the average grade for each element.

I can find the SUM over the sheets (=SUM(Scottish!B3, Scottish! D3 . . .) - Instr1 and the AVERAGE the same way.

However - if a pupils is off, the cell is blank. I have found various formula which work in the format (A1:A100) but not (Scottish!B3, Scottish! D3, Jazz!B3, Jazz!D3)

Does anyone have any ideas how to accomplish this?

Thank you in advance

Dave
__________________
PrinceMusic is offline   Reply With Quote
Old 05-16-2008, 03:27 PM   #6
Team Manager, Articles
Analyst
Rangemaster, TSF Academy
 
Glaswegian's Avatar
 
Join Date: Sep 2005
Location: Glasgow
Posts: 38,483
OS: Win XP Pro SP3 / Win 7 Pro

My System


Hi and welcome.

Complex formula such as this are not my forte, but this might help

http://www.mrexcel.com/forum/showthr...ultiple+sheets

__________________
Iain - Defender of the Haggis and all things Scottish.
I don't help by PM - post in the Forums.



PC Safety & Security::PC running a bit slow?::Photographers Corner
Glaswegian is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

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

BB code is on
Smilies are on
[IMG] code is on
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Post a Question


» Site Navigation
 > FAQ
  > 10.0.0.2


All times are GMT -7. The time now is 09:20 PM.


Copyright 2001 - 2014, Tech Support Forum

Windows 7 - Windows XP - Windows Vista - Trojan Removal - Spyware Removal - Virus Removal - Networking - Security - Top Web Hosts