Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP not working with data from PeopleSoft
I have data which has come from PeopleSoft. One file is a list of current
employees (about 4,500 rows) including a unique ID and I am looking up their date of birth in another file which is list of all employees, past and present (just over 7,000 rows). For some reason the vlookup is not returning any data and is just showing the formula in the cell. I feel the problem is something in the format of the data coming from PeopleSoft but can't think of anything else to try. I use vlookup heaps so don't think there is anything wrong with the formula. I have checked that the employee IDs are numbers, not text and that there are no leading or trailing spaces. I have checked that the DOB is a date format. I tried copying the data from the all employees file into a sheet in the current employees file and doing the lookup from there. I tried naming the lookup range. I tried sorting by ID even though I don't need to as it's exact match. Data in the files looks something like: ID Name DOB 10001 Jones,Fred 10005 Smith,John and in the "all employees" list, the DOB uses the first date format (*dd/mm/yyyy). Formula reads: =VLOOKUP(A2,Sheet4!$A$1:$B$7291,2,false) Any help will be greatly appreciated! -- Celia from sunny Australia |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP not working with data from PeopleSoft
If your problem is it "is just showing the formula in the cell", then your
cell is formatted as text. Format it as General, and re-enter your formula. It should work then. Regards, Fred "Celia" wrote in message ... I have data which has come from PeopleSoft. One file is a list of current employees (about 4,500 rows) including a unique ID and I am looking up their date of birth in another file which is list of all employees, past and present (just over 7,000 rows). For some reason the vlookup is not returning any data and is just showing the formula in the cell. I feel the problem is something in the format of the data coming from PeopleSoft but can't think of anything else to try. I use vlookup heaps so don't think there is anything wrong with the formula. I have checked that the employee IDs are numbers, not text and that there are no leading or trailing spaces. I have checked that the DOB is a date format. I tried copying the data from the all employees file into a sheet in the current employees file and doing the lookup from there. I tried naming the lookup range. I tried sorting by ID even though I don't need to as it's exact match. Data in the files looks something like: ID Name DOB 10001 Jones,Fred 10005 Smith,John and in the "all employees" list, the DOB uses the first date format (*dd/mm/yyyy). Formula reads: =VLOOKUP(A2,Sheet4!$A$1:$B$7291,2,false) Any help will be greatly appreciated! -- Celia from sunny Australia |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP not working with data from PeopleSoft
Fred, many thanks - that worked fine. I think I might have tried
reformatting the cell previously (I tried so many things I can't remember them all !!) but the trick (as you mention) is to re-enter the formula because it doesn't "fix" automatically with the reformatting. Have a great day. -- Celia "Fred Smith" wrote: If your problem is it "is just showing the formula in the cell", then your cell is formatted as text. Format it as General, and re-enter your formula. It should work then. Regards, Fred "Celia" wrote in message ... I have data which has come from PeopleSoft. One file is a list of current employees (about 4,500 rows) including a unique ID and I am looking up their date of birth in another file which is list of all employees, past and present (just over 7,000 rows). For some reason the vlookup is not returning any data and is just showing the formula in the cell. I feel the problem is something in the format of the data coming from PeopleSoft but can't think of anything else to try. I use vlookup heaps so don't think there is anything wrong with the formula. I have checked that the employee IDs are numbers, not text and that there are no leading or trailing spaces. I have checked that the DOB is a date format. I tried copying the data from the all employees file into a sheet in the current employees file and doing the lookup from there. I tried naming the lookup range. I tried sorting by ID even though I don't need to as it's exact match. Data in the files looks something like: ID Name DOB 10001 Jones,Fred 10005 Smith,John and in the "all employees" list, the DOB uses the first date format (*dd/mm/yyyy). Formula reads: =VLOOKUP(A2,Sheet4!$A$1:$B$7291,2,false) Any help will be greatly appreciated! -- Celia from sunny Australia . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP not working with data from PeopleSoft
Tip...
Re-enter doesn't necessarily mean retype. Change the format to General then, with the cell still selected, either double click in the cell then hit Enter or, hit function key F2 then hit Enter. -- Biff Microsoft Excel MVP "Celia" wrote in message ... Fred, many thanks - that worked fine. I think I might have tried reformatting the cell previously (I tried so many things I can't remember them all !!) but the trick (as you mention) is to re-enter the formula because it doesn't "fix" automatically with the reformatting. Have a great day. -- Celia "Fred Smith" wrote: If your problem is it "is just showing the formula in the cell", then your cell is formatted as text. Format it as General, and re-enter your formula. It should work then. Regards, Fred "Celia" wrote in message ... I have data which has come from PeopleSoft. One file is a list of current employees (about 4,500 rows) including a unique ID and I am looking up their date of birth in another file which is list of all employees, past and present (just over 7,000 rows). For some reason the vlookup is not returning any data and is just showing the formula in the cell. I feel the problem is something in the format of the data coming from PeopleSoft but can't think of anything else to try. I use vlookup heaps so don't think there is anything wrong with the formula. I have checked that the employee IDs are numbers, not text and that there are no leading or trailing spaces. I have checked that the DOB is a date format. I tried copying the data from the all employees file into a sheet in the current employees file and doing the lookup from there. I tried naming the lookup range. I tried sorting by ID even though I don't need to as it's exact match. Data in the files looks something like: ID Name DOB 10001 Jones,Fred 10005 Smith,John and in the "all employees" list, the DOB uses the first date format (*dd/mm/yyyy). Formula reads: =VLOOKUP(A2,Sheet4!$A$1:$B$7291,2,false) Any help will be greatly appreciated! -- Celia from sunny Australia . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
Importing from Peoplesoft | Excel Discussion (Misc queries) | |||
PeopleSoft report spits out 1 row into 2 rows -how to make 1 row | Excel Worksheet Functions | |||
VLOOKUP not working due different formats in lookup data & table? | Excel Discussion (Misc queries) | |||
vlookup formula not working with data on separate sheet | Excel Worksheet Functions |