# Excel question about combining totals for each person on separate pages

Discussion in 'Business Applications' started by hotskates, Oct 4, 2008.

Not open for further replies.
1. ### hotskatesThread Starter

Joined:
Jan 10, 2002
Messages:
6,375
I'll explain first what I have done and then what I want to do next and can't figure out how.

I have an Excel page set up for my company and it is to keep track of donations. My Excel page has 8 sheets (one for each fundraising event). For example one sheet has donations from a fundraiser dinner listing each person, dollar amount and date. Another sheet lists donatons from a mailing we sent out which included contribution envelopes. This sheet also lists the name, dollar amount and date.
On the front page I have titled it "Total Donations" and list each fundraising event along with their totals.

At the end of the year I send out the donation thank you letters including their tax receipt and their total amounts donated. I was wondering if there is a formula for combining every donation for each specific person so I don't have to back track through each sheet searching for that persons name. For example, if Joe Black donated at three events, I'd like to see which events, amounts and dates and a total of all three. I could have an additional sheet listing every donor in alphabetical order and maybe the totals could be on that page.

Any ideas?

2. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
Yes.

Combine the eight sheets into one. Add an extra column headed "Event", then sort by "Name" and the donations per person will be grouped.

Then add a pivot table to get the totals.

HTH

3. ### hotskatesThread Starter

Joined:
Jan 10, 2002
Messages:
6,375
Thanks bomb..... please tell me how to combine the 8 sheets into one Do I right click onto a new sheet and "select all sheets" and then paste them onto a the new sheet.....or what?

4. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Actually, you could probably do it with a formula in a new worksheet and then list that on your "Total Donations" worksheet.
First you would need a list of everyone who has contributed - easy enough, I would just copy all the names to a new workbook, make a pivot table of them and then copy and paste special, values, just the names from the pivot table, back into the new worksheet.
Then I would use indirect lookups in 8 columns - one for each worksheet - for each name, and in a final column I would sum across those 8 columns in each row. Than that could be linked back to your "Total Donations" sheet.

5. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Alternatively, the free add-in for Excel, ASAP Utilities
http://www.asap-utilities.com/
has a function which will combine selected areas from multiple worksheet onto a new worksheet. Then you could have all the names and donation values in one sheet and, with a bit of cutting and pasting (if your data is name|amount, the combined data would be written as name|amount|name|amount...) and then with a pivot table you could get sums for each person.

6. ### hotskatesThread Starter

Joined:
Jan 10, 2002
Messages:
6,375
Thanks slurpee.......sounds like you know your way around Excel
I'll try some of your suggestions when I get back to work next week....and bomb's ideas too I've never taken an Excel class, just learned on my own...but sometimes I run into something I need for it to do and I just can't figure it out. Its an amazing program though!!
I love that you can use colors to coordinate everything too.

7. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
Yup, my post was pretty light on "how to" on account of it was just about bed-time.

The reason I suggested combining was your initial description, which suggests that all 8 sheets have the exact same layout; person -- dollars -- date in that order. If that's correct, then (unless you have 10s of 1000s of rows per event) there's really no practical reason to have one sheet per event and you could be making work for yourself by doing so.

Here's an answer to your "how to".

1. Don't add a new sheet -- add the data from sheets 2 through 8 to sheet 1.

2. On sheet 1, go to the top left cell of data. Press End then Down to go to the bottom cell (provided you have no blank rows), then press Down again. You're now at the cell where sheet 2 data needs to be added.

3. Go to sheet 2, and to the top left cell (if you have headings in e.g. row 1, ignore those). While holding down Shift, press End followed by RightArrow, followed by End, followed by Down. All required data on sheet 2 should now be selected, so ...

4. Copy -- switch to sheet 1 -- paste.

Repeat 2 to 4 for the remaining 6 sheets. You'll get the hang of the methods after a sheet or two, so in all we're talking a 10 - 15 minute "exercise". It's a toss-up whether downloading/installing/getting your bearings with ASAP would beat the above method in a race, but by all means check out ASAP since it has lots of other time-savers.

Finally: you don't strictly need all your data on one sheet to construct a pivot table, but it's easier if you do. And once you have a table, you can use it for other stuff than "totals per person", such as "totals per event per year" so you can see where you need to concentrate your efforts (if that's the case). You'll be able to get help with pivot tables here if it goes that way.

HTH

8. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Self-taught here too - although I must admit that most of the really good tricks I know I picked up here or from other sites that people here suggested or linked to. Compared to bomb #21 or Zack Baresse or jimr381 or rollin again or...well, a host of others, I am a novice. But happy to keep learning!

9. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
And, of course, you could haul all this into Access, and not have to worry about multiple sheets, just have a field listing the event or something like that....

10. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
What happened? Did an enormous bushel suddenly descend upon you or something?

(BTW, dunno where you think I am but it's currently 0900 here. )

11. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
I realized that after I sent you a message - forgot you are rather far across the water (you seem so normal! )
And, I have been carousing all night...

As for the bushel, well, before they changed the site my sig was "I used to think I knew a lot until I came here" and it still holds true. (I quit doing farm work when I was 16, so not likely that it will really happen!)
The gang here amazes me - I am proud that I am accepted as one of the regular posters, to be honest.
But compared to you (mostly) guys who know VBA so well (why is this considered a misspelling by the site??? - but you know, you, Zack, OBP) or someone like jimr381 who knows Office inside, out and sideways, I just muddle along.
Still, as much as I can help, I love doing so.

So, hotskates, sorry to steal your thread for the moment, but, hey, the people on this site are generally amazing and deserve to be told so now and then!

12. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
I came here looking for somewhere I'd be less out of my depth. That worked for a while; then ziggy, rollin, jimr (list is long and not necessarily in that order but for now definitely ends with Zack) showed up and ...

One day I'll build my own forum where only I can post answers. Then I'll be best.

Gotta say, I miss MustBNuts, a former Access guru. And Annie, obviously.

(sorry h/s)

13. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Gotta agree with you - the list is long and sometimes I get frustrated, say, by seeing a post about Access and then it says last post by OBP...how do I compete with that???
Don't think I ever knew MustBNuts. Annie? Sigh....
On a bright note, it is great to have Zack back!

14. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Wow, and I thought I was a bad post whore! No wonder you have 4k posts slurp! Seriously, wow, thanks guys. Slurp, put yourself up on that pedestal too, so I don't have to (or I could ). And as for me, it is great to be back!!

Maybe we could think about some other keyboard shortcuts here? Perhaps some Ctrl + Shift + Spacebar (assuming the data is contiguous), Ctrl + C, Ctrl + Page Up (until back at the combining sheet), Ctrl + V, Ctrl + Page Dn (until back at the next sheet to copy), wash/rinse/repeat..

15. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
Just what program are you using? Is that a feature in 2007???

As Seen On

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!