I started this thread almost 2 years ago and the solution was very useful during years 2010-11 and (adapted) for 2011-12.

However, I cannot get it to work for year 2012-2013. I cannot see where the formula is wrong so perhaps someone can help me. I am still using Excel 2003.

2011-2012 formula:

=LOOKUP(1E+100,'\\Pfadc01\company data\ADMINISTRATION\Excel\Accounts 2011-2012\Bank 11-12\[2011-2012 Bank balance.xls]PF'!$F:$F)

2012-2013 formula:

=LOOKUP(1E+100,'\\Pfadc01\company data\ADMINISTRATION\Excel\Accounts 2012-2013\Bank 12-13\[2012-2013 Bank balance.xls]PF'!$F:$F

All the folders and workbooks are named the same, with the exception of the years (i.e. 2011-2012 became 2012-2013).

When I use this formula, it just returns zero in the cell in my Payments workbook where I need the current bank balance.