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
