Excel97 - "Smart" Sum Totaling Across Various Sheets (VBA?)
Excel97 - "Smart" Sum Totaling Across Various Sheets (VBA?)
(OP)
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!
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?)
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".