wrote:
I had a formula in an Excel workbook called Payments
to update the bank balance during 2011-2012:
=LOOKUP(1E+100,'\\Pfadc01\company data\ADMINISTRATION\
Excel\Accounts 2011-2012\
Bank 11-12\[2011-2012 Bank balance.xls]PF'!$F:$F)

This worked perfectly well until I needed a similar
formula for 2012-2013. I have amended it as appropriate
but it only returns 0.00 on my Payments sheet.
This is the formula I am using:
=LOOKUP(1E+100,'\\Pfadc01\company data\ADMINISTRATION\
Excel\Accounts 2012-2013\
Bank 12-13\[2012-2013 Bank balance.xls]PF'!$F:$F)

Can anyone see where I have gone wrong please?

Quite possibly nowhere.

The formula returns the last numeric value in column F. Presumably, 0.00 is
the last numeric value.

If you want the last non-zero numeric value, you can use the following
normal-entered formula (just press Enter as usual) if the last non-empty
cell contains a numeric value, not text.

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