Tech Support Forum Excel formula over multiple sheets
 Site Map Posting Help Register Rules Search Today's Posts Mark Forums Read

# 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

 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.

 12-07-2005, 01:16 PM #2 Team Manager, Articles Analyst Rangemaster, TSF Academy     Join Date: Sep 2005 Location: Glasgow Posts: 39,419 OS: Windows 10 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
 12-07-2005, 01:28 PM #3 Henry     Join Date: Apr 2005 Posts: 3,490 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
12-07-2005, 02:30 PM   #4
Team Manager, Articles
Analyst

Join Date: Sep 2005
Location: Glasgow
Posts: 39,419
OS: Windows 10 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?

__________________
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

 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
 05-16-2008, 03:27 PM #6 Team Manager, Articles Analyst Rangemaster, TSF Academy     Join Date: Sep 2005 Location: Glasgow Posts: 39,419 OS: Windows 10 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

 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 OffTrackbacks are Off Pingbacks are Off Refbacks are Off Forum Rules

 » Recent Discussions Under Review: Brighten up your workspace – An...