Hi, I am developing a financial report that uses a standard report layout in a single Excel Tab but can be updated from the same data source depending upon selection of:
A. a month (Apr, May or Jun) then
B - options for
1. Divisional,
2. Department or
3. Cost Centre
noting that Divisional and Department selections must remain for Cost Centre to work. Also for Department the Divisional selection must remain but CCtr can be "ALL".
Not looking to use Pivot Tables (eg Slicers) thus looking at formula, eg IFS or other option. Seems like 9 different options if I combine the month (Apr=10) and a number for Div, Dept or CCtr; eg AprDiv=11, MayDiv=12 and JunDiv=13
MayDept=14, MayDept=15 and JunDept=16
JunCCtr=17, MayCCtr=18 and JunCCtr=19
Appreciate any advice or options.
Thanks, Kym
It would be very helpful if you attached a sample file which is reflective of your actual file (no confidential material included). We can then work with a file and not have to guess at what yours looks like.
Hi Alan, Thank you. I have set up the attached sample. Essentially the User will select the Month (Apr, May or Jun). Then review a report at Divisional level and after may choose to then look at Department level within that Division , then Cost Centre level within that Division/Department. But all in the same report layout in the same Tab/location.
Effectively - 3 options for months, following by 3 options for each month.
Regards, Kym
Where do you get stuck? I don't see any of those different options you mention.
Below example is one way to sum per the criteria. There are of course other methods.
=SUMPRODUCT(
($O$2:$Q$12)*
(($L$2:$L$12=B11)+($M$2:$M$12=B11)+($N$2:$N$12=B11))*
($O$1:$Q$1=$B$2)
)
If you only intend to show one row and not all three then the summary is enough, based on the current layout of the data where for example CCtr11 only exists for Div5 and Dept9.
If you intend to show all three rows as per your sample file, then you need to add logic to when to do the summary and not. For example:
=IF(OR(B12="All";ISEMPTY(B12)),
SUMPRODUCT(
($O$2:$Q$12)*
(($L$2:$L$12=B11)+($M$2:$M$12=B11)+($N$2:$N$12=B11))*
($O$1:$Q$1=$B$2)
);
0)
I hope this gives some help for you to move forward with the report build.
Br,
Anders
Thank you to all for the advice. I ended up using Power Query and Power Pivot Table then A Choose formula with 9 variables. Work well and file size not overly large for sharing.
Will be working on a different item now and will post. 🙂
Hi, I am still having some issues. I have attached a sample file, with the main issue being that the current Ranges for Apr, May and Jun are dependent on a formula that extends beyond the actual data from the Pvt (eg to row 24).
Can a to row number (eg =+MATCH(2,1/(K:K<>""),1)) result be used in the Range Names; eg currently to row 15 that adjusts if PvT data is updated.
I hope the file makes it a bit clearer and appreciate any advice.
.
Since you mentioned PQ in an earlier post, why not use it to create the table you need? In the attached file you'll find a solution that I labelled PQ 1 and an alternative that uses the PIVOTBY function based on another PQ solution, labelled PQ 2.
Hi Riny, Thank you. I will explore it further, unfortunately where it will be used they are not that familiar with PQ and also there updated version does not have PIVOTBY function yet.
@kcrett Fair enough, but the neat thing about PQ is that, once properly set up, it can run without much user intervention. All the users need to know is how to refresh the report when data has changed. And that’s often easier and safer than to give them a sheet with complex formulas that can easily be messes up.
Hi Riny, agree PQ is likely the better option if I can get them to agree.
