P& L in Excel - how do I suppress current month?
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?