Summarizing data from different worksheets 1

[notsoevilgenius]

I have worksheets tabbed by month.

Column A - Subscribers (unique)
Column B - Agents (duplicates)
Columns E thru ?? Agents (unique)



I added a tab called Report

The user wants to be able to pick a single agent and get a yearly total. There can be different subscribers (A) and agents (E thru ??) each month. Column TOTALS are on different rows in each sheet. I want to run through the worksheets and make a list (without duplicates) of each agent and make a total. What would be cool is if I can put them in a pick list (in alphabetical order) and the user can click one and get the output in a particular cell. I'm using Excel 2016, but the user will be using Excel 2013. I'm pretty good with excel and formulas. I haven't used form controls or active x controls, but I have used VBA for some macros.

Thanks

 

[SkipVought]

Hi,

I have worksheets tabbed by month.

The user wants to be able to pick a single agent and get a yearly total.

Your workbook structure makes it very difficult to do what the user wants. Excel is designed to work with NORMALIZED TABLES; i.e. the data from all 12 tabs in ONE TABLE on ONE SHEET. Naturally there would need to be a column for DATE, the day, month, year, representative of each tab.

In addition, Agents in "columns E thru ??" Is also a problem of normalization that is another big processing problem.

Whoever designed your workbook has foisted needless processing problems on the report analyst.

If you had a properly designed table, a simple AutoFilter with a SUBTOTAL() function above the table, can give you statistics for any filtered selection. Very simple! To do what you want to do with your current structure would be much more complex. Simply consolidate your data!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[notsoevilgenius]

Hi Skip. Yeah, I knew this wouldn't be easy. I don't have an issue providing the user a consolidated table of agents with yearly totals if that's the easy way, but it has to be done by formula or code. I can run through the individual sheets and define name ranges if I need to as well. I already have to grab the sheets out of other workbooks that tracks these commissions from other projects by month-year. I don't want to have to mess with the worksheet data and risk the possibility of screwing it up. The big issue is that both new subscribers are always happening and agents can come and go. That means the number of rows and columns by can increase each month sheet.

I originally thought to create an access database where a user can upload each month's raw data into that, strip out the data that doesn't pertain to the client with a query and provide a database form where the user can identify specific individual entries and modify them for "splits". Then make reports from that data. This is first time I've had to try to develop something like this. Every job I've done has been individual record entry setup. I don't know enough yet to add each consecutive month's data programatically and inputting the data record by record would be nearly a full time job for one person.

 

[SkipVought]

Does this workbook need monthly tabs? Can you not consolidate?

You mentioned, "I already have to grab the sheets out of other workbooks that tracks these commissions from other projects by month-year." Is this how your corporate system works: on a bunch a workbooks? No corporate database/tables?

"I don't know enough yet to add each consecutive month's data programatically and inputting the data record by record would be nearly a full time job for one person"

That might depend on how you acquire this data each month. But it could be an automated process.

The first order of business would be to consolidate the data and then to work on an AppendMonth process.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[notsoevilgenius]

Does this workbook need monthly tabs? Can you not consolidate?

No, but that's the way I get it. The original workbooks are still intact. How do I consolidate it without typing (or cutting and pasting) 6 months of data? I want to avoid that.

You mentioned, "I already have to grab the sheets out of other workbooks that tracks these commissions from other projects by month-year." Is this how your corporate system works: on a bunch a workbooks? No corporate database/tables?

The client is a broker. Each month they log into each corp they sell for and download the monthly commission report and remove all the data that doesn't pertain to them. The use keeps each month in a single workbook. Everything up until now has been in house with a 6 or less agents - commission checks are passed out around the office. The new data I'm now tracking is different and for a whole geographic region and that's now 31 agents. Now s specific corporation pays my client for every agent within this region that sells it's product whether they work for the client company or not and they are responsible to send out commission checks, plus bonuses for amounts over a certain dollar amount. It makes it a little more complicated. This just started in Jul and I've just been tasked to do the work.

That might depend on how you acquire this data each month. But it could be an automated process.

The data is downloaded from the corporate site each month in xls format. It has company id, subscriber id, product id, agent id, effective date and a plethora of both useful and useless (at least to my client) data.

The first order of business would be to consolidate the data and then to work on an AppendMonth process.

Great I'm back to where I started. LOL. Sorry it's been a long day and I'm trying to do 3 things at once. I may be able to go back to the source and get the raw monthly reports from the corporate company. I'll be able to find out tomorrow.

Thanks for your help Skip. I'm glad you are here to bounce this stuff off when I need it.

 

[notsoevilgenius]

I just looked to see if I could just cut and past aug data under jun data and then sep under aug etc. That would assume the columns in all the worksheets are the same with the exception of new agents in which case I'd only need to use the column headers from the last month. The only thing we can ASS U ME that no one ever make our job easy.

 

[SkipVought]

6 months of data" ???

I thought you had a tab for each month. So why the heartburn for doing 6 or 12 copy 'n' paste in order to get a solid starting point? You want to "automate" that one time task? I'm a programmer and I wouldn't.

But on the other hand, you state, "The original workbooks are still intact." so you could develop the process to grab the NEW monthly data and then apply that process to grab the historical data from those afore mentioned workbooks. THAT is what I would do.

Actually, this problem would be best addressed in forum707. You won't be able to get where you want to be easily with formulas. You mentioned Named Ranges. What ranges did you name?

Please describe the structure of the table in the workbooks that you import into your workbook.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[notsoevilgenius]

You mentioned Named Ranges. What ranges did you name?

I didn't name any ranges, but I was thinking I could go through and name the agents and totals, but doing that would probably take longer than the cutting and pasting.

But on the other hand, you state, "The original workbooks are still intact." so you could develop the process to grab the NEW monthly data and then apply that process to grab the historical data from those afore mentioned workbooks. THAT is what I would do.

I think I'll have to grab two months of historical data (directly from the source data) to develop the process. Automating the process would eliminate the need for workbooks.

Actually, this problem would be best addressed in forum707: VBA Visual Basic for Applications (Microsoft).

I'll start there. Thanks.