I am a huge fan of pivot tables, and often use them to help transform raw data into a different table, by “mirroring” data from a pivot table, where I have a table to the left of the pivot table, and I want to have the table show the same value the pivot table has, or possibly transform it with a formula. Often you can just choose the cell reference that the pivot table has and you are done… But sometimes the pivot table may have multiple columns, that vary, and you wish to do some sort of lookup on the pivot table… Traditionally, to do this, it would require creating a GETPIVOTDATA formula. These have always been a bit complex, and now with PowerPivot being built-in to Excel 2016, they are even MORE complex. This is why some people choose to use cube functions instead, which can now be done on PowerPivot tables…. But I have tested both of these, and they are very slow when you have a lot of rows. So, I have gone back to an old school method of using the MATCH function (with absolute references) to determine which column a particular heading is in, and then use the INDEX function to select the value from that heading. This old school trick turns out to be a LOT more efficient than using GETPIVOTDATA, and thus I thought I should share this with any other Excel nerds who read my blog.
Month: September 2016
When Highlands Community Charter School recently was looking to switch to a new SIS, we ultimately chose PowerSchool, and for the past month, I’ve been working intensely to migrate from our previous system (School Pathways) to PowerSchool. Overall this process has been smooth, but it has also highlighted to me some clear problems that PowerSchool has, and why one shouldn’t only choose a product because it has the most market share.
My first review of various SISes is that of Aspen by Follett. When Highlands Community Charter School recently was looking to switch to a new SIS, Aspen was in our top 3 choices, and only barely lost out to PowerSchool. During our review process, I had the chance to look at a sandbox system (demo) of their product for about a week, and we asked a lot of questions to their sales rep, Dylan Holcomb. As a matter of disclosure, I should note that Dylan was a friend from high school, but I think this review is fairly objective, as there are clearly things I don’t like about the product, along with many things I really like. I have written about Aspen previously also.