Forum

Notifications
Clear all

[Solved] Formula for a financial report with a number of options

10 Posts
4 Users
0 Reactions
353 Views
(@kcrett)
Posts: 11
Active Member
Topic starter
 

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

 
Posted : 01/06/2025 9:19 am
Alan Sidman
(@alansidman)
Posts: 235
Member Moderator
 

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.

 
Posted : 01/06/2025 9:46 am
(@kcrett)
Posts: 11
Active Member
Topic starter
 

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

 

 
Posted : 01/06/2025 10:41 am
Anders Sehlstedt
(@sehlsan)
Posts: 977
Prominent Member
 

@kcrett

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

 
Posted : 02/06/2025 1:36 am
(@kcrett)
Posts: 11
Active Member
Topic starter
 

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

 
Posted : 04/06/2025 12:56 pm
(@kcrett)
Posts: 11
Active Member
Topic starter
 

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.

.

 

 
Posted : 06/06/2025 3:17 pm
Riny van Eekelen
(@riny)
Posts: 1264
Member Moderator
 

@kcrett 

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.

 

 
Posted : 06/06/2025 8:44 pm
(@kcrett)
Posts: 11
Active Member
Topic starter
 

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. 

 

 
Posted : 08/06/2025 2:26 pm
Riny van Eekelen
(@riny)
Posts: 1264
Member Moderator
 

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

 
Posted : 08/06/2025 2:58 pm
(@kcrett)
Posts: 11
Active Member
Topic starter
 

Hi Riny, agree PQ is likely the better option if I can get them to agree.

 
Posted : 09/06/2025 6:55 am
Share: