Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
Milin

Milin

Technical User
Jan 5, 2000
33
US
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.<br>
<br>
What I am wanting to do (I assume with the help of VBA) is to generate a summary report that does something like, &quot; 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.&quot;<br>
<br>
Maybe this is something more aking to MS Access, but I really want to keep it within Excel<br>
<br>
Thanks much!
 
Sort by date Sort by votes
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 &quot;datasheet view&quot; of tables available so you can make it look just like a spreadsheet to the users!<br>
<br>
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 &quot;naming cells&quot; 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 &quot;Insert/Name/Paste&quot;.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

kwbMitel
  • Locked
  • Question
I can't add sheets to my Excel file because it's protected.
Replies
4
Views
2K
StilLearningAlot
StilLearningAlot
duytoi
  • Locked
  • Question
How to add a head to the search results in Excel 2016 using VBA
Replies
2
Views
2K
Andrzejek
Andrzejek
sxschech
  • Locked
  • Question
Excel Conditional Formatting and Non VBA solutions
Replies
17
Views
576
sxschech
sxschech
PSchubert
  • Locked
  • Question
Excel - SUMIFS() works with one range, but doesn't with another nearly identical range 2
Replies
6
Views
468
Andrzejek
Andrzejek
pendle666
  • Locked
  • Question
Excel 365 - Multiple SUMIFs with percentages
Replies
5
Views
459
combo
combo

Part and Inventory Search

Sponsor

Back
Top