Populating one spreadsheet from data in other spreadsheets in same workbook

**dardesmom** · 06-01-2007, 11:23 PM

I need to have totals from individual worksheets automatically enter into cells in a master spreadsheet.

What I am doing is keeping track of donations collected from individual departments - each on their own worksheet. I would like to have the totals of each page automatically enter & update onto a master worksheet that would show the totals from each dept and then give me a total of all those.

I am not that well versed in Excel. I have been able to set up the individual worksheets and the master.....but can not figure out how to accomplish what I want with the Master tally sheet.

On the individual worksheets I also want to set it up to give me the average donation per person. So if I total the # of donors and the total $$$ amount...what formula do I use to get the average?

**Paul** · 06-01-2007, 11:37 PM

To work with data on other sheets, you can use the sheet names in the formulas, like this:

=SUM(Sheet2!A2:A10) or =Sheet2!A4+Sheet4!B7-Sheet3!C1

These formulas would go on your master sheet where you want to show the results.

As for the formula for average, it is:
=AVERAGE(your_range/cells)

where "your_range/cells" is a range of cells or list of cells or named ranges. For example:
=AVERAGE(A8:A16) or =AVERAGE(A9, A10, A15, A16)

**dardesmom** · 06-02-2007, 12:31 AM

Thank you for the speedy reply. I tried the average first, since it is the easiest. Something is not right tho....for ex: we have $50, donated by 2 people.....now I know that means $25/person....however using hte formula as you gave it to me, it calculates to $26

A second line has $50 donated by 1 person and it calculates as $25.50

Another line we have $60 donated by 3 people....it is calcualting as $31.50

What am I doing wrong? Here is how it looks so far.

MOB - FLR 1 $50.00 2 $26.00
MOB - FLR 2 #DIV/0!
MOB - FLR 3 $50.00 1 $25.50
MOB - FLR 4 #DIV/0!
MOB - FLR 5 $60.00 3 $31.50
DISPATCH #DIV/0!
STEVEDORES #DIV/0!
B I #DIV/0!
MIA - OFFICE #DIV/0!
MIA - WRHSE #DIV/0!
SHOPS #DIV/0!
VEH REC - TL - PK2 #DIV/0!
INTERCHANGE #DIV/0!
#DIV/0!
TOTALS 6 $6.00

**Paul** · 06-02-2007, 12:46 AM

The way you're using average, it won't work. I thought you had a list of donations, like $50, $40, $50, $20, $100 - and wanted to average those.

It appears you have the total $, and the total # of people (donations), so you can just use the formula:
=(cell address with $ amt) / (cell address with # of people)

For example, if $50 is in cell B4, and 2 is in C4 (people), in D4 you could use
=B4/C4

This will give you 25.

From your results with the AVERAGE function, it appears you're averaging the 50 and 2, which would equal 26. (50+2=52, then divided by 2 cells equals 26) Same is occurring with your other attempts.

**dardesmom** · 06-02-2007, 01:12 AM

Thanks Paul...that did it for that feature.

Now, I'll have to work on the other.

**dardesmom** · 06-02-2007, 01:30 AM

I am typing into the cell I want the total carried over the formulas as you said, substituting my sheet name for sheet 1, etc. But I am getting an error.

For ex, sheet 1 is named MOB-FLR1 - the donation column is cells C6 thru C31
so I entered the formula as =SUM(MOB-FLR1!C6:C31) and I get the error saying #NAME? so I tried just calling it SHEET2, thinking that is what EXCEL would recognize it as.....and then I get the error REF#

Am I missing something?

**VBA Noob** · 06-02-2007, 03:26 AM

Try

=SUM('MOB-FLR1'!C6:C31)

Missing the ' symbols

VBA Noob

**dardesmom** · 06-02-2007, 09:00 AM

Thank you Noob, but if you read my post just before yours, you will see that is exactly what I was typing and it did not work.

**VBA Noob** · 06-02-2007, 09:08 AM

Yours was =SUM(MOB-FLR1!C6:C31)

mine is =SUM('MOB-FLR1'!C6:C31)

Spot the difference. ' in red

VBA Noob

**dardesmom** · 06-02-2007, 09:27 AM

My apologies, Noob! When I read :

Originally Posted by VBA Noob

Missing the ' symbols

I took that to mean I was to leave those marks out! That is what I get for trying to work BEFORE the morning coffee has settled in!

I did enter it your way now ... I am getting the error #REF!

**VBA Noob** · 06-02-2007, 09:42 AM

Try in say sheet 1

Enter =

then select sheet MOB-FLR1 and then select your range.

Also attached is an example

HTH

VBA Noob

**Paul** · 06-02-2007, 09:43 AM

Can you post the exact formula you are using? If you're getting a REF error in a simple formula like this, perhaps the sheet name is just misspelled? Paste your formula here and we'll take a look at it.

**dardesmom** · 06-02-2007, 10:08 AM

To make this easier and less painful for you (hopefully), I am going to attach my file, so you can see what I am trying to do

I truly appreciate your assistance and patience. I am very limited in my knowledge of how to make Excel do what I know it can do.....I want to learn more about it - time is just my issue.

**dardesmom** · 06-02-2007, 10:11 AM

I just realized the problem! The caffeine must be kicking in finally!

I was not including the spaces in the sheet name when I used the fornula!
DUH! Talk about a senior moment!

Thank you both for your help! I have it now!

**dardesmom** · 06-02-2007, 10:31 AM

Next question....is there a formula I can use to populate the # of people column on the Totals sheet from each sheet by Excel being able to tell how many entry lines I have on the corresponding sheet? Each line in a certain range (A6...A31) would equal one person.

**dardesmom** · 06-02-2007, 01:39 PM

I was able to figure out a way to get the people count into my totals sheet.

However, now I am finding out that the data is not updating in the totals sheet when new data is entered into the dept sheets. Why is that and what can I do to make it update automatically?

Sorry to be full of so many questions. I was volunteered for this projuect and I am trying to set up a way to easily keep track of incoming donations and be able to send the reports to other people in email version. I figured EXCEL was the way to go. See below...

Originally Posted by dardesmom

To make this easier and less painful for you (hopefully), I am going to attach my file, so you can see what I am trying to do

I truly appreciate your assistance and patience. I am very limited in my knowledge of how to make Excel do what I know it can do.....I want to learn more about it - time is just my issue.

**VBA Noob** · 06-02-2007, 02:16 PM

Maybe

=SUM('MOB FLR 1'!D:D)

or in B5 dragged down to B9

=SUM(INDIRECT("'MOB FLR "&ROW(A1)&"'!D:D"))

and to count people

=COUNTA('MOB FLR 1'!$B$6:$B$65536)

or

=COUNTA(INDIRECT("'MOB FLR "&ROW(A1)&"'!$B$6:$B$65536"))

and in your average column

=IF(ISERROR(B9/C9),"",B9/C9)

VBA Noob