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? Help would be greatly appreciated!

<(E-Mail Removed)> 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)