# Excel formula over multiple sheets



## spxtechsupport

Hi,

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

Thanks,

Just me.


----------



## Glaswegian

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


----------



## Spatcher

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 :grin:

EDIT: But my way is intended for using a function for just one cell across multiple sheets.


----------



## Glaswegian

HenryVI said:


> 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 :grin:
> 
> EDIT: But my way is intended for using a function for just one cell across multiple sheets.


You following me Henry? :grin: 

As I said, I think we need some more info here. :smile:


----------



## PrinceMusic

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


----------



## Glaswegian

Hi and welcome.

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

http://www.mrexcel.com/forum/showthread.php?t=319397&highlight=average+multiple+sheets


----------

