Let us assume the spread-sheet uses the standard accounts layout of figures for each month, so 12 columns of figures in all, for January to December. You want to indicate the current month with a number (1 for January, 2 for February etc) and have this month’s figure automatically displayed separately.
One approach is to use a very complicated IF statement:
IF(P1=1,C4,if(P1=2,D4,if(P1=3,E4 … and so on up to P1=12
However this is long-winded and complex and therefore invites errors. Our rule of thumb is if you get to three levels of Ifs, there is probably a simpler way to do it. And in this case there is a much simpler way.
The OFFSET Function
OFFSET is a neat little function which allows you to say, from any cell go 3 to the right and 4 down – or, better, use cell references. So to extract this month’s accounts:
P1 is the number of the month. B4 is the column before the financial data starts, and the 0 indicates you don’t want to move down a row. So it will take the value of C4 when when P1 is 1, D4 when P1 is 2 and so on.
If you have any Excel questions or hints & tips you would like shared get in touch!
If you would like any further information on the Excel courses we train, go to our website
Remember – If you’ve been on a Happy Computers course you get 2 years free helpline access. Call the helpline on 0207 375 7373.