Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP Mystery
One of the Excel users here at UM has encountered a bizarre problem. He is generating a report from our accounting system which he pastes into an excel spreadsheet. He is using VLOOKUP to get data from the pasted report which is used to update MTD and YTD accounts. In general, it works fine except for this oddity: In account numbers that are structured as XXXXX-XX (where X is a numeric digit), the VLOOKUP function works fine. In some situiations where account numbers are structured without a suffix XXXXXX VLOOKUP will not return information UNLESS the users goes to the target sheet and simply clicks in the cell that should be updated. At that point, the data comes across. Whats Gives? Thanks for the help. Rick -- RHubbard ------------------------------------------------------------------------ RHubbard's Profile: http://www.excelforum.com/member.php...o&userid=27406 View this thread: http://www.excelforum.com/showthread...hreadid=469192 |
#2
|
|||
|
|||
simply clicks? or is it F2 then Enter
Check to make sure that calculation is turned set on Automatic Tools, Calculation (tab), calculation: [x] Automatic --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "RHubbard" wrote in message ... One of the Excel users here at UM has encountered a bizarre problem. He is generating a report from our accounting system which he pastes into an excel spreadsheet. He is using VLOOKUP to get data from the pasted report which is used to update MTD and YTD accounts. In general, it works fine except for this oddity: In account numbers that are structured as XXXXX-XX (where X is a numeric digit), the VLOOKUP function works fine. In some situiations where account numbers are structured without a suffix XXXXXX VLOOKUP will not return information UNLESS the users goes to the target sheet and simply clicks in the cell that should be updated. At that point, the data comes across. Whats Gives? Thanks for the help. Rick -- RHubbard ------------------------------------------------------------------------ RHubbard's Profile: http://www.excelforum.com/member.php...o&userid=27406 View this thread: http://www.excelforum.com/showthread...hreadid=469192 |
#3
|
|||
|
|||
Hi Rick,
I assume that XXXXX-XX is the lookup value -- that is, the user plugs in an account number to return via VLOOKUP a MTD or YTD figure. If that assumption's wrong, please correct me. What is the VLOOKUP function returning before the user clicks in its cell? Nothing? An error value? (BTW, you might find the Tools | Formula Auditing | Evaluate Formula tool useful here, if you have a sufficiently recent version of Excel.) It shouldn't matter, but you might want to check how he has the Calculate option set. Manual? Automatic? Hard to tell yet what's going on, but: XXXXX-XX is a text value, and XXXXX given that X = numeric digit is a number. Again, though, this shouldn't make a difference. In the XXXXX-XX case, is there an exact match in the lookup table? What range_lookup option is he using? -- C^2 Conrad Carlberg Excel Sales Forecasting for Dummies, Wiley, 2005 "RHubbard" wrote in message ... One of the Excel users here at UM has encountered a bizarre problem. He is generating a report from our accounting system which he pastes into an excel spreadsheet. He is using VLOOKUP to get data from the pasted report which is used to update MTD and YTD accounts. In general, it works fine except for this oddity: In account numbers that are structured as XXXXX-XX (where X is a numeric digit), the VLOOKUP function works fine. In some situiations where account numbers are structured without a suffix XXXXXX VLOOKUP will not return information UNLESS the users goes to the target sheet and simply clicks in the cell that should be updated. At that point, the data comes across. Whats Gives? Thanks for the help. Rick -- RHubbard ------------------------------------------------------------------------ RHubbard's Profile: http://www.excelforum.com/member.php...o&userid=27406 View this thread: http://www.excelforum.com/showthread...hreadid=469192 |
#4
|
|||
|
|||
On Tue, 20 Sep 2005 10:27:08 -0500, RHubbard
wrote: One of the Excel users here at UM has encountered a bizarre problem. He is generating a report from our accounting system which he pastes into an excel spreadsheet. He is using VLOOKUP to get data from the pasted report which is used to update MTD and YTD accounts. In general, it works fine except for this oddity: In account numbers that are structured as XXXXX-XX (where X is a numeric digit), the VLOOKUP function works fine. In some situiations where account numbers are structured without a suffix XXXXXX VLOOKUP will not return information UNLESS the users goes to the target sheet and simply clicks in the cell that should be updated. At that point, the data comes across. Whats Gives? Thanks for the help. Rick Is XXXXXX a number? If so, it may have been pasted as TEXT, where VLOOKUP is looking for a number (or vice versa). --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP using a cell calculated with NOW returns Error | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |