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

[Milin]

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!

 

[elizabeth]

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;.