I have set up an Excel spreadsheet to produce monthly management accounts importing the data directly from Sage. I pull the monthly account movements into a sheet and then pull all the data from there into the various schedules as separate sheets.
On the P&L and BS I want to suppress the printing of months later than the reporting month. The need arises as transactions are often being input for the current month when we are reporting for the previous month. A relatively elegant way of achieving this is by using conditional formatting, where the condition checks whether the month is greater than the reporting month, if it is then it makes the font white, and so is not visible or printed.
However, I have a problem with the year to date totals on the P&L which are just the sum of the monthly figures. Even if a month's figures are not visible, using conditional formatting, they are still included in the total.
The only other solution I have thought of is to use IF statements in every calculation to test if month is greater than reporting month then "" else calculation. However this makes the calculations very convoluted and it is then very difficult to fault find.
Does anyone have a more efficient solution?
Neville Ford
Replies (5)
Please login or register to join the discussion.
Twisting both Lynne and Mike's methods further
you could use a dynamic range formula that might be less prone to someone editing an array formula without realising that you need to press Ctrl + Shift + Enter to quit.
So, if you want the year to date for figures spreading across the page starting from B5 you could use:
=SUM(OFFSET($B5,0,0,1,$A$1))
Where A1 contains the number of columns/months across the page you want the SUM to spread.
This is saying:
Starting from B5 add the contents of a range 1 row high (ie row 5 in this case) and n columns wide based on the number in $A$1
You could also select a ComboBox from the Forms menu bar where the Input range was a range containing the months in your year and the Cell Link was A1 (in this instance). You can then select the month that you want from the drop down and the SUM above will adjust as necessary
Use sub totals and 'VLOOKUP'
Hi Neville
The simplest way I can think of quickly is to insert a YTD column after each month in your master sheet. Name the whole range including column numbers inserted at the top (1 - 24), then in your presentation sheet you can have two cells at the top which have either M or Y in one, and the month No. in the other.
By using 'VLOOKUP' in conjunction with these, you can make the presentation sheet display any month typed in at the top, M or YTD, or both, instantly!
There must be other ways; this is just the quickest and simplest that came to mind.
P.S. For simplicity, so that m# works 1 - 12, you will need to multiply the input M/No by 2 and take 1 for Month only in the VLOOKUP, e.g. M6 Month = column 11!
Good example of an application of "array" formulae
I find array formulae are very powerful - hitting "Ctrl+Shift+Enter" instead of just "Enter" has opened up a whole new world! ;-)
Enter months in row 2:
A2 = eg. Jan-05 (date formatted)
B2 = eg. Feb-05
etc through to Dec-05
... and YTD at the end (in cell M2)
Demo result, eg. turnover, in row 3:
A3 = 100
B3 = 200
etc
Cell A1 is your control cell - for illustration, put Apr-05 there to indicate that you want April YTD.
Your cell M3 (YTD dependent on the month in A1) formula is
=SUM(IF(A2:L2<=A1,A3:L3))
...if you only hit "Enter" you get a #VALUE! error but if you hit "Ctrl+Shift+Enter" it will work.
Alternative solution using Index and SUM Index
Neville
This is an example of how I approach the Y-T-D totals.
12 months in cells E1:O1
Column P is blank in this example
Monthly sales totals E4:O4
Period selector (1-12) in B1
To generate total sales for period n,
set B1 to period n.
Use the formula -
=INDEX(E4:P4,$B$1)
To generate total sales for say periods 1-6, put 6 in B1
Use the formula -
=SUM(E4:INDEX(E4:P4,$B$1))
Mike