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.

 

[SkipVought]

Hi,

If all your tables were Structured Tables, then the issue of varying row length is moot.

Why is a macro necessary? Your formulas will all be something like...
[tt]
=MIN(Table1[XXX TIME VIEWED])
[/tt]

 

[RBLampert]

Hi, Skip. As I've mentioned before, I know nothing about structured tables, so I don't know where to start there. Since I don't know anything about them, I'm looking to a macro to fill in the skeleton of the worksheet. I'll look into structured tables and see if that will help, but it's important to note that the size of each range (the number of cells in the column of data that makes up the range, not the length of any row) will vary.

 

[SkipVought]

Sorry I was referring to the number of rows or the column length is moot.

ST is simple, simple simple! With MANY great features!

Select in your table.
Insert > Tables > Table.

I'd name your tables
tAAP, tQAR, etc or anything you might prefer.

Writing formulas is quite easy with IntelliSense feature in Excel, which is why I use the tXXXXXX naming convention.

 

[RBLampert]

I'm good with simple! I'll be looking into this.

 

[RBLampert]

OK, I've looked. Turns out I've already used the structure table concept once and liked it.

That said, it doesn't entirely solve my problem. While I can see a way to do away with all those extra worksheets, I think I still have the problem of defining and naming the ranges. The data that all these ranges will apply to is going to be new every month. That means the source data worksheet is going to be new every month. Once it's populated and appropriately manipulated so that it's ready to feed the analysis process, I'll still need to filter the data by training video title so I can then define the ranges and name them (unless there's a way to do this without filtering first). That's the work I still want a macro to do because I'm facing the possibility of 100 or more separate ranges.

Pulling the data off of that one source data worksheet is A LOT simpler than off of those other worksheets. But now the question is, can I create a template worksheet that will reference the right ranges without getting all wonky on me? If I can create a template workbook that I simply import the monthly data into, that solves several problems but I still need a tool to define and name those ranges, and the macro seems to be the right tool.

 

[SkipVought]

Wow! What a terrible design: a new sheet for a new month's data for a similar table structure. This would never be done in a database, like you company's, but it is easy to make this elementary blunder in a workbook, and unfortunately, often is done by novice spreadsheet users. It makes data analysis extremely clumsy and difficult. It is not conducive to Excel's data analysis features, as is now demonstrated by your conundrum.

What you need is a SINGLE table of each type, that will need at least one additional column for a Month Of date. Then your aggregations will be based on a Month Of date, making it possible to also report on Year To Date, Quarter data and comparisons to previous years' data.

I admit that's a significant change of design and process. But your current design is very limiting in many ways!

 

[RBLampert]

Yeah, well, that's because this is the first time I'm doing this and I'm still in the process of figuring out what to do with the data to produce meaningful information. I've thought about how to do trend analysis but have been more focused on getting this basic stuff done first.

Yes, I certainly could separate out the data by video title and then by month. Fortunately there's not that much data to work from.

But that STILL doesn't address the question of how to identify the ranges so the spreadsheet can do the basic number-crunching. Can we focus on that problem first, please? It's not going to go away, even if I break the data out the way you suggest.

 

[SkipVought]

I still don't know the structure of your basic tables. How many sheets/tables are used in a particular month and what are the basic structures? A download would definitely be in order.

You still do not get it, however. If you had x basic tables, then that's all you would need, each with the addition of a Month Of date. Given any specific Month Of date, that month's statistics could be extracted: using either several PivotTables or several queries, or even using a more complex formula including OFFSET(), MATCH(), COUNTIF()

You had previously stated,

"For simplicity's sake, let's say three of the worksheets are named AAP, QAR, and QFR..."

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

And now we have,

"The data that all these ranges will apply to is going to be new every month. That means the source data worksheet is going to be new every month."

So how do you name these new sheets in a way that your macro will recognize the new data?

BTW what time zone are you in. I'm in USA Central.

 

[RBLampert]

OK, let's not get into a urinating contest over who does or does not "get" what. I need you to understand that while I've used Excel for something like 20 years, I am neither a database manager/designer nor a VBA programming expert. I've taken on this task because I'm the one guy in my working group--all of us volunteers--who had enough knowledge to start doing this and the time and willingness to learn everything I know I don't know. It should be no surprise, then, that I'm doing a lot of things in very inefficient ways--they're the best I know how. When there are better ways, I want to learn them, even if it means discarding work I've already done.

After I looked into structured tables, I realized that I could do away with those extra worksheets for what I was intending to do. Then, when you suggested capturing trend data, I realized I would still need them, but for a different purpose, and I could do away with the Summary worksheet instead.

You're right that an example would be a big help to you, so I've attached one to this post. I've highlighted the groups of cells that would be this particular month's named ranges on the All Data worksheet. Just in this relatively sparse example, there are 40 ranges to be named if the structured table formulas are to work and I'd much rather have a macro do that every month; I don't have the time to do it manually.

On the Summary worksheet, only the formulas in rows 4 and 6 link to the correct worksheet. The others are there just as part of the development process. Each row would have to have its worksheet reference changed. Based on your recommendations, I can see these rows of formulas being moved to their respective worksheets with the formulas linked instead to the All Data worksheet.

I have an idea of how I could make all of this work with the new data coming in each month: manipulate the data into the format you'll see on the All Data worksheet separately (those macros are all built and work), then copy it into the (blank) All Data worksheet, from which it would be filtered and copied to the video-specific ones (AAP, QAR, QFR, etc.) for analysis. The trend data would also be developed on those specific worksheets.

Finally, I live in Arizona, so I'm on Pacific time until the rest of the country goes off daylight savings time, at which point Arizona reverts to Mountain time.

 

[SkipVought]

Are you able to download the file you uploaded? I cannot.

 

[RBLampert]

Yikes! Yes, I could. Got an alternative?

 

[SkipVought]

Okay I solved my problem. I have your workbook. Give me a few minutes to look over your comments and the workbook.

 

[SkipVought]

Well this will take a bit more than a few minutes, seeing that I also have some other things on the docket this evening. But I will be responding and perhaps others will also.

 

[RBLampert]

No worries! Take your time. It's not a rush at this end. Glad you were able to download it.

 

[SkipVought]

Okay, I'v got half way thru with an approch using your All Data sheet with Named Ranges or Structure Table Fields.

Still don't know what happens when you get new month's data???

Here are the formulas...
[pre]
Viewers # Total =SUMPRODUCT(--(Module_Title=$B3))
# Unique =SUMPRODUCT(--(Module_Title=$B3)*(1/COUNTIF(UniqViewers,UniqViewers)))
Actual # of slides =SUMPRODUCT(--(Module_Title=$B3)*(TotalSlides))/SUMPRODUCT(--(Module_Title=$B3))
# of Sl Minimum =MinViewTotalSlidesViewed(B3)
Average =AVERAGEIF(Module_Title,B3,ViewTotalSlidesViewed)
Average* =AVERAGEIFS(ViewTotalSlidesViewed,ViewTotalSlidesViewed,">0",ViewTotalSlidesViewed,"<=19",Module_Title,B3)
Maximum =MAX(IF(Module_Title=B3,ViewTotalSlidesViewed))
Maximum* =MAX(IF((Module_Title=B3)*(ViewTotalSlidesViewed<=19),ViewTotalSlidesViewed))
[/pre]

Here is the function for Min # of Slides Viewed

Function MinViewTotalSlidesViewed(sMODUL As String)
    Dim i As Integer
    Dim MODUL As Range, VTSV As Range
    
    Set MODUL = [Module_Title]
    Set VTSV = [ViewTotalSlidesViewed]
    
    MinViewTotalSlidesViewed = 999999
    For i = 1 To MODUL.Count
        If MODUL(i) = sMODUL Then
            If MinViewTotalSlidesViewed > VTSV(i) Then
                MinViewTotalSlidesViewed = VTSV(i)
            End If
        End If
    Next
    
    Set MODUL = Nothing
    Set VTSV = Nothing
End Function

Date issues can be addressed like this to include only JUNE 2015...
[tt]
=SUMPRODUCT(--(Module_Title=$B8)*(Date>=DATE(2015,6,1))*(Date<DATE(2015,7,1)))
[/tt]

More to come for th remaining fields.....

 

[SkipVought]

Actually here's a MIN formula that seems to work, rather than a user defined function
[tt]
=MIN(IF((Module_Title=B3)*(ViewTotalSlidesViewed>0),ViewTotalSlidesViewed))
[/tt]

 

[RBLampert]

WHEW! It's going to take me some time to understand all of that! Lots of research and learning to do.

Here's what I'm thinking as far as dealing with each month's new data is concerned.
1. I'll build a "master" workbook that contains a blank All Data worksheet plus the individual worksheets for each training video.
2. At this point I have two options:

a. I can copy or move the data we already have into each of those worksheets and run the minimum, average, and maximum calculations there.​

b. The other option is to run only the calculations in the video-specific worksheets and save the numerical values but not the formulas.​

Each of these options has advantages and disadvantages. Either way, naming the ranges via a macro would make this process go much faster. Also, there's not that much data I'll have to do this with, since we've only been collecting it since late last November. This will also let me start building the trend charts for each of these videos.
3. Once all of this is done, I'll clear the data from the All Data worksheet.
4. When I receive the file with the new data, I'll run the macros that turn it into what you see in the All Data tab right in that source file.
5. I'll copy that data into the blank All Data worksheet, then execute whichever Step 2 option I settle on on the new data.

The more of this that I can automate through macros, the better, but I intend to follow the same code-a-little, test-a-little process I used for building the set of macros that turn the raw data into the format and contents you see in the All Data worksheet.

Kind of a long answer to a short question. From a database management point of view it may not be the perfect or optimal process but it's what I'm envisioning now.

 

[SkipVought]

Yes, you'll need to write code to load the All Data sheet.

Let's start at the beginning.

How do you acquire new data?

How many different data sources are there? The source sheets you have in your workbook really all have the same table structure and I'd consider them multiple pieces of one source. So asking in a different way, how many data sources and within each data source, how many pieces?

You seem to be stuck on naming the ranges the as a complicated matter. It is trivial. Using Structured Tables implies all the ranges you need. I used Named Ranges in the All Data sheet, as a shortcut for developing the formulas I posted last night. They could have been done just as easily using ST notations, but the formulas would have been longer. These ranges reference an entire column of data. We will need to add some additional criteria to our formulas to discriminate on a date range.

BTW, the MAX & MIN formulas are entered as ARRAY formulas (cntr+shift+ENTER)

I did fail to mention that I added a column, to the large table in All Data, that concatenates the Module Name and the Viewer name, to enable the count of unique viewers (notice the second formula that references this new column range.

Look forward to your answers.

 

[SkipVought]

Check out faq68-4725 that explains how SUMPRODUCT() works with array criteria.