We can all agree that it is best to encourage our users to conduct their analysis in Prophix, but sometimes things do not work out that way, and we are forced to use Microsoft Excel.
I found myself in one of these situations recently where I used a Prophix template to generate about 15 different Excel report files. Each file had anywhere from 1 to 65 worksheets, and each worksheet had between 10 and 100 rows with a total at the bottom. Since I used a template to generate these files, each of the worksheets were formatted the same, with a dimension member key as the worksheet name and a dimension member name in the same cell of each worksheet. However, about 30 minutes before I was scheduled to leave for a family event, I was asked to add a leading worksheet (“Lead Sheet”) to each of the files that would provide (at a glance) a list of all of the worksheets along with a total from each of the worksheets.
The bad news is that these files were not generated using Prophix’s helpful “Table of contents” feature, but the good news is that I was able to fulfill this request by using dynamic worksheet references in Excel. And, I made it to my event on time! I cannot believe that I am the only one that has ever been asked to do something like this at quitting time, so I hope you find my attached example file to be helpful.
Dynamic Lead Sheet Example.xlsx (29.9 KB)
I was not expecting to learn something about Excel when I logged in just now - but Thank You for sharing! This may come in very handy.
Hi @jason.norman.1 can you show how your lead sheet looks because when you export a report to excel you do get an index where you have an index of all the following worksheets? Thanks!
Hi @aakash.jhaveri My lead sheet example takes this one step further, by creating dynamic formulas that also pull values from each of the worksheets. If you look at the file that I uploaded with my original post, you should be able to see how all of it fits together. Cheers!
Hi- So I checked your excel and I think we can come close to your final goal doing most of it in Prophix and one final step in excel (and you can actually automate that using macros)
So,
In design mode- First add a row and sum the values, I’m calling it ‘Grand Total’
Next, click on options and go on ‘Save As Excel’ tab, switch on the ‘Customize worksheet naming’ and bring in < Page member > In my case that < Page member> is Department
Save it and export it to excel.
It will look like this and because of our < Page member> you can actually see your departments as the worksheet name as well on our index
Now you can use your ‘Sumif + indirect’ formula
I have used
=SUMIF(INDIRECT("'*"&A7&"*'!A:A"),"Grand Total",(INDIRECT("'*"&A7&"*'!N:N")))
So with this just with one excel formula you can have your file ready!
Here is the file.
sumif indirect.xlsx (95.3 KB)
2 Likes
@jason.norman.1: great example and congratulations on getting to your event on time. Thanks for sharing the file and for the detailed notes and instructions.
1 Like
