Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Lookup problem in Excel 2003
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! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Lookup problem in Excel 2003
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex value lookup? (Excel 2003) | Excel Discussion (Misc queries) | |||
Excel 2003 lookup help | Excel Programming | |||
Excel 2003 Lookup function | Excel Worksheet Functions | |||
Copy and paste problem Excel 2003 to Word 2003 | Excel Discussion (Misc queries) | |||
Does 2003 excel have training on V Lookup | Excel Worksheet Functions |