Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel97 - "Smart" Sum Totaling Across Various Sheets (VBA?)

Excel97 - "Smart" Sum Totaling Across Various Sheets (VBA?)

Excel97 - "Smart" Sum Totaling Across Various Sheets (VBA?)

I work in an advertising marketing department where we track initial response to an Ad and the method in which the response was recieved, via Excel spreadsheets. There is a column that contains a specific Ad code, a column for quantity of phone responses, and a column for quantity of card responses (so, reading across, Ad# AA0199 has 10 phone responses, 20 card responses) These forms are in table style and are generated twice a week each week during a given month. All the sheets are formatted and set up exactly the same. The Ad#s always repeat across the sheets.

What I am wanting to do (I assume with the help of VBA) is to generate a summary report that does something like, " go out across all of the sheets and look in the AD# column and find the matching AD# codes. If the codes match, sum the quantity of phone responses and sum the quantity of card responses across all the sheets. Make one line-item per unique Ad# code."

Maybe this is something more aking to MS Access, but I really want to keep it within Excel

Thanks much!

RE: Excel97 - "Smart" Sum Totaling Across Various Sheets (VBA?)

Yes, this is just one of the functions that databases are built for. Spreadsheets are great tools, but sounds like you are growing beyond them. Once you imported your tables into Access, you would find many other advantages. If you import your tables into Access, you can use a report Wizard to do this for you easily, and so much more. And a nice touch is that Access has a "datasheet view" of tables available so you can make it look just like a spreadsheet to the users!

If you're determined to stay with Excel, you don't necessarily need to get into code. One alternative is to create a sum for each item that you want to add, then name (see "naming cells" or something like that) the cell that contains the sum. On your summary page, create a calculation that uses names instead of cell numbers. Use "Insert/Name/Paste".

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close