Vlookup error after database download

[cgclower]

I have a vlookup formula that references a range on another sheet that gets information downloaded into it from an Access database when the file opens.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
My formula is correct, but I get an #N/A error anyway. The only way I can fix it is to manually click into the target cell and get the curser in there. Then the formula works fine. That is, it works fine until I close and reopen the sheet, at which point the download happens again and the error returns.
<o> </o>
Why? How can I make this go away?
<o> </o>
Thanks!
<o> </o>
Formula in cell B1: =VLOOKUP(A1,'OtherSheet'!C:D,2,FALSE),

 

[hiker95]

cgclower,

Formula in cell B1: =VLOOKUP(A1,'OtherSheet'!C:D,2,FALSE)

See Excel Help for VLOOKUP.


Try filling in the rows in the range:

=VLOOKUP(A1,'OtherSheet'!C1:D100,2,FALSE)

 

[cgclower]

Tried that, didn't work.

Also tried messing around with the format of the cells (number vs text vs general, etc). Nothing.

Thanks for the help though!

 

[Yahya]

i think your data in other sheets not formatted as number
try this steps:
1)select data in column C
2)then from data tab , choose text to columns
3)just press finish

then try your formula

 

[hiker95]

cgclower,

Excel Workbook
	C	D
1	1	A
2	2	B
3	3	C
4	4	D
5	5	E
6	6	F
7	7	G
8	8	H
9	9	I
10	10	Bingo!!!
11	11	K
12	12	L
13
OtherSheet

Excel Workbook
	A	B
1	10	Bingo!!!
2
Sheet2

If the above is not helpful, then can we have some screenshots of the two worksheets?


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[Aladin Akyurek]

Tried that, didn't work.

Also tried messing around with the format of the cells (number vs text vs general, etc). Nothing.

Thanks for the help though!

See the recipe Yahya posted...

If the text number assumption is correct, the following...

=VLOOKUP(A1&"",'OtherSheet'!C:D,2,FALSE)

if it works, would support that.

 

[Bradium]

I have had this problem with data from sources outside excel. The solution I found via this board is to run this code over the data range.

Selection.value = Selection.value

Regards

Brad

 

[hiker95]

cgclower,

If none of the ideas above work, then can we have some screenshots of your two worksheets?


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[cgclower]

Thank you all for the fantastic suggestions! The problem is indeed the fact that the data in the destination cells comes from Access. Consulting with some people I know who are quite familiar with the situation (benefits of living in Seattle), apparently it is just a bug/quirk in Excel. All kinds of lookups (including offset/match formulas) will react the same way.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
The solution I went with is the one Yahya gave: I just added a line of code after the Access import populates the sheet to run text-to-columns:
<o></o>

Columns("C:C").TextToColumns Destination:=Range("C1")

<o></o>
Works great!
<o></o>
Thanks again to everyone for the help!