Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]() I'm having trouble with a "VLOOKUP" formula. I use Excel to open a text file where the text is separated by spaces. I go through the motions of establishing what values are to be in what columns and then click finish. I take this information and past it into another existing Excel spreadsheet. In the first column are numbers (let say 1010,1011, 1020, 1021, etc) These numbers represent drawing numbers. The values are sorted in ascending order. The second column is the revision number of the drawing, which could be a letter or a number, usually only a single digit or alphanumeric character. The third column is the text description of the drawing. On another tab of the spreadsheet the user inserts a value which is intended to be one of the drawing number from column 1. On the same row, next column, the VLOOKUP formula looks at the cell where the user inserted the drawing number and should place the revision number from column 2 for that drawing number. Formula - =IF(A1="","",VLOOKUP(A1,'Data Tab'!$A$1:$C$100,2)) The formula returns a #N/A, but the referenced cell has the value needed in order for the revision number to be displayed. It's only when I go back to the "Data Tab" and manually overwrite the cell with the same value that the formula works. This leads me to believe that the pasting the values from the previous spreadsheet formats the cell in some what that causes the formula to fail. I've changed the format of the column in the "Data Tab" to "Number" with no decimal places, I've checked for spaces in the data and there aren't any, what the hell am I not seeing? -- Dan Marr |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]() Try running the "Clean" function on the data. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Dan Marr" wrote in message I'm having trouble with a "VLOOKUP" formula. I use Excel to open a text file where the text is separated by spaces. I go through the motions of establishing what values are to be in what columns and then click finish. I take this information and past it into another existing Excel spreadsheet. In the first column are numbers (let say 1010,1011, 1020, 1021, etc) These numbers represent drawing numbers. The values are sorted in ascending order. The second column is the revision number of the drawing, which could be a letter or a number, usually only a single digit or alphanumeric character. The third column is the text description of the drawing. On another tab of the spreadsheet the user inserts a value which is intended to be one of the drawing number from column 1. On the same row, next column, the VLOOKUP formula looks at the cell where the user inserted the drawing number and should place the revision number from column 2 for that drawing number. Formula - =IF(A1="","",VLOOKUP(A1,'Data Tab'!$A$1:$C$100,2)) The formula returns a #N/A, but the referenced cell has the value needed in order for the revision number to be displayed. It's only when I go back to the "Data Tab" and manually overwrite the cell with the same value that the formula works. This leads me to believe that the pasting the values from the previous spreadsheet formats the cell in some what that causes the formula to fail. I've changed the format of the column in the "Data Tab" to "Number" with no decimal places, I've checked for spaces in the data and there aren't any, what the hell am I not seeing? -- Dan Marr |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
vlookup error | Excel Worksheet Functions | |||
vlookup() error #n/a | Excel Worksheet Functions | |||
Vlookup #N/A Error | Excel Worksheet Functions | |||
vlookup error!! | Excel Worksheet Functions | |||
vlookup error | Excel Worksheet Functions |