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

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