Excel 2010 macro: filling a summary worksheet with formulas referencing other worksheets

[RBLampert]

I'm trying to build a summary worksheet that will consist largely of rows of formulas that calculate minima, averages, maxima, and other results from data in other worksheets in the same workbook. I know the correct formulas. I know how to specify the range of data to be used within each worksheet. I know the names of the worksheets. What I don't know is how to build a macro that will properly populate each calculation cell in the summary worksheet that will properly name the worksheet and range the formula is to apply to.

For simplicity's sake, let's say three of the worksheets are named AAP, QAR, and QFR and use the basic MIN, AVERAGE, and MAX functions: =MIN('Worksheet Title'!Worksheet_Range), =AVERAGE('Worksheet Title'!Worksheet_Range), and =MAX('Worksheet Title'!Worksheet_Range). Let's say that the ranges (each a column of cells that will vary in length each month when this data is pulled), should be named XXX_Time_Viewed, XXX_Time_Percent, XXX_Slides_Viewed, and XXX_Slides_Percent, where XXX refers to the worksheet name. And let's say the rows on the summary worksheet have the same titles (first column) as the worksheets they refer to.

It seems like there should be a compact way for a macro to populate each cell in each row with the correct formula which points to the correct worksheet and range. Maybe with a for...each loop (or several) that references one or more arrays containing the worksheet names and/or ranges?

Or would it be better take a different approach entirely--say, do all the calculations on the source worksheets and only copy the results to the summary sheet?

If that's too unclear, I can upload a conceptual example of what this summary worksheet would look like.

 

[RBLampert]

OK, answers:

How do I acquire the data? It's sent to me at the beginning of the month from my organization's national headquarters. They draw the data from Brainshark, the web site that hosts the videos.

How many different data sources are there? Just one, as far as I can tell: Brainshark.

How many "pieces of data"? Not quite sure how to answer this other than to upload the source data file for the file I submitted earlier.

Range names: This is a concern in two situations:

1. If I only have the min/average/max calculations (and not the data) in the video-specific worksheets, then I need to tell the formulas in those worksheets exactly where to go to get their data on the All Data worksheet (for example, =MIN(All Data!Worksheet_Range). It seems to me that range names are the only way to do that, because I have to draw the data only from specific cells within a column. I don't see how the structured tables make that happen automatically.​

2. If instead I keep ALL historical data for each video in its specific worksheet, along with the formulas, then it seems that I'm going to run into the same problem of identifying exactly which cells a formula needs to use when calculating that month's min/averages/maxima. Again, I don't see how the structured table would solve that requirement without providing those additional criteria you mention to focus on a particular month's data.​

What am I not understanding?

Array formulas: yes, got that. Very handy tool.

Unique viewers: If I calculate just for a specific month's worth of data, I've got an array formula ({=SUM(1/COUNTIF('Worksheet Title'!Worksheet Range,'Worksheet Title'!Worksheet Range))}) that works as it should (I can manually verify that it's producing the right answer consistently) without getting into concatenation. It might need a bit of tweaking to focus in on the exact range of data I want it to look at if it's being used in a worksheet containing more than one month's worth of data--there's that named range thing again.

Hope that helps!

 

[SkipVought]

How do I acquire the data? It's sent to me at the beginning of the month from my organization's national headquarters. They draw the data from Brainshark, the web site that hosts the videos.

do they supply an Excel workbook or other? What is the typical file name?

I meant to ask, how do you acquire the data in the workbook? Is this acquisition automated?

1. If I only have the min/average/max calculations (and not the data) in the video-specific worksheets, then I need to tell the formulas in those worksheets exactly where to go to get their data on the All Data worksheet (for example, =MIN(All Data!Worksheet_Range). It seems to me that range names are the only way to do that, because I have to draw the data only from specific cells within a column. I don't see how the structured tables make that happen automatically.

Okay, I simply renamed your Structured Table to be more applicable to AllDataModViewData rather than Monthly. (personally, I'd reduce this name to something more manageable like tVwDta. You see alot of real estate is chewed up by a long table name) I also named 2 cell ranges; ThisPeriodStart & NextPeriodStart, containing 6/1/2015 and 7/1/2015. In B3 id the first Module Title, Web Reporting - Quarterly Activity Report. The returned value is 6.
[tt]
=SUMPRODUCT((AllDataModViewData[Module Title]=$B3)*(AllDataModViewData[Date]>=ThisPeriodStart)*(AllDataModViewData[Date]<NextPeriodStart))
[/tt]
Then Unique viewers, where I added a concatenated column that will propagate as new rows are added, so there's no need to actually perform anything new. The return value is 4.
[tt]
=SUMPRODUCT((AllDataModViewData[Module Title]=$B3)*(AllDataModViewData[Date]>=ThisPeriodStart)*(AllDataModViewData[Date]<NextPeriodStart)*(1/COUNTIF(AllDataModViewData[UniqViewers],AllDataModViewData[UniqViewers])))
[/tt]
and so on, using the same formulas I posted last night but with the addition of the date range.

So you clearly do not need a multitude of extra sheets. You apparently have a macro that imports the data and chops it up into chunks that are no longer necessary, the imported data just needs to be appended to the All Data Structured Table.

I can still detect that you do not understand the power and simplicity of the SUMPRODUCT() function and the array formulas for aggregating data from a single range for any number of specific criteria in multiple ranges.

 

[RBLampert]

The file comes to me in .csv format. The filename is usually something like "June Viewing Details by Presentation.csv", sometimes with the month, sometimes not.

I don't know how the staff at headquarters acquires the data. It appears the acquisition is NOT automated. I believe my contact there manually pulls a report from the Brainshark database each month. I do know that she can select which fields from the database get downloaded--I've seen an extract that contained all the fields and there are many she doesn't pull--and the time period the extract covers.

Maybe I don't need extra sheets but at the moment I don't understand how to develop and manage the information I want to get without them. That's not your fault, it's my lack of knowledge of and experience with this stuff (as I keep reminding you).

I created the macros that run on the original data to both get rid of data or columns that I didn't need and to create the data I felt I did need. Some of those changes are for clarity, others are for accuracy. For example, Brainshark rounds down all percentages of viewing time that exceed 100% to 100%. That's inaccurate, so I needed to calculate the correct values. You may think there's wasted effort there, and maybe there is, but from my perspective, it's not.

You're right that I don't understand SUMPRODUCT. Until you brought it into this discussion, I'd never heard of it. Array formulas are new to me and I'm only beginning to get comfortable with them.

Remember that I'm a volunteer, this is not my primary job, and I have NO formal training in this. I'm learning as I go and pushing the boundaries of my knowledge WAY out every time I figure out how to do something new. Give me time and space to learn, please.

 

[SkipVought]

So you get a .csv. That still does not tell me if you open the file with Excel as a separate workbook or if you import the data from the file into an existing workbook???

Okay, you're not comfortable with array functions with multiple criteria. Therefore you do not want to accumulate the data from month to month in a single table. You would rather chop each month's data into Module Title chunks so that each range will be discrete. That will be a lot of ranges!

Have you had any experience doing queries? You could loop thru all the Module Titles in the imported data and query each into another sheet/table, and use the same procedure to load the aggregations for each. So there would ONLY values and no formulas on the Summary sheet.

Short of that, what would you like from us?

 

[RBLampert]

Currently I open the .csv file with Excel. To date I have not imported it into an existing workbook, in part because the information the .csv file contains does not provide all of the information I need or contains that isn't accurate, as described above.

It's not entirely accurate to say I'm "not comfortable with array functions with multiple criteria." I've never done it before. I wouldn't mind learning how to do that if it would make this job easier. I realize this isn't a teaching forum, however.

And it's not that I "would rather" chop each month's data..., it's all that I know how to do.

No, I have no experience with queries.

What would I like from you--or someone? Help that's appropriate to my basic level of knowledge and experience, not (figurative) "Ph.D. level" stuff that does me no good.

 

[SkipVought]

Well I guess that the exchange to this point has educated me to a degree, that I feel I must, in effect, comply with your request to cut off your leg as opposed to going through a long and difficult recovery, in a matter of speaking.

I greatly enjoy helping members come to a satisfactory solution. Don't know how I'll fare in this quest. There may be others who are will to take up this gauntlet.

I guess we're back to the beginning. We have your workbook, with the Summary sheet and the All Data sheet. I really see no need for chopping up this data. Since you would need multiple formulas in each column, I would suggest that VBA code might be simpler to keep straight. Loop thru the Summary sheet Module Name column to fill in the aggregations across. That might be the summary of the approch. Each column's calculation could be a separate Function.

Something we never addressed is the Part 1, Part 2, etc. what's that all about? THAT ought to be in a separate column, NOT concatenated to the Module Name, the KEY value that joins the tables.

 

[RBLampert]

I think we need to let this sit and cool for a while, Skip. I want to do some solo research and experimenting.

I'm sure there are more efficient ways to do all this but I don't know them and I don't want to just blindly follow your code without understanding it. Especially if it doesn't quite work. That means I'd have a severe learning curve to climb and it's not your job to get me up it. My resources for making that climb here, especially quickly, are very limited.

You were right about using the data to feed trend analysis. The Summary worksheet only supports a single month's worth of data, so a different approach, one that supports the ability to track trends, is more desirable. But that gets us back to square 1.

As for that "Part 1, Part 2" stuff, some of these training videos come in multiple parts in order to make them less time-consuming for viewers. In the training syllabus that defines who's to watch what when, these "Parts" are assigned to different months, although folks can watch them any time they want. Sure, the part numbers could be separated out into a separate column but since the video titles include the part numbers, I'm not sure I see any value in doing so.

 

[SkipVought]

You have DATA for AFA Organization.

You have no Summary for AFA Organization!

You have data for Maintaining 501(c)(3) Tax-Exempt Status.

You have no Summary for Maintaining 501(c)(3) Tax-Exempt Status.

I'm going to sleep on it.

 

[RBLampert]

I'd really stop worrying about the Summary page. It was only partially developed to begin with, and from month to month some videos are going to get viewed while (many) others are not, meaning no data for them.

Just let it rest for a while. I'll be back in touch when I need your help again.

 

[SkipVought]

FYI, I believe I've taken Excel courses offered by Btainshark.

 

[RBLampert]

Have to look into that. The only way I know how to access Brainshark is through my organization's link, however. I know lynda.com has some Excel courses. I've viewed parts of one of them.