VLookup Won't Work unless Pressing Enter in Cell

[bdunk]

I am trying to setup a simple VLookup between two sheets. The VLookup code is as follows:

=VLOOKUP(A2,TopDown!$A$5:$E$62,5,FALSE)

After writing the formula and copying it down the column I get the #N/A error in all my cells but one. This cell's content is "V17811". This is the only cell in my table array that starts with an Alpha character. All cells are set to 'Text'. To get any other cell to work correctly I need to select the cell in my Table Array. In the Formula Bar above I place my cursor at the end of the number and press the Enter key. This then puts the Green corner in the upper left hand corner of the cell and shows the Exclamation point notification that something is not right.

Why does this make it work?
What is happening to the cell when I press enter that makes it work?

Any help is much appreciated.

Brian

 

[gmanos]

[For weight lookup]=[@material]&" "&[@[thickness '[mm']]]
and as a value, the first row is
[For weight lookup]=E 6
the second
[For weight lookup]=EA2 4

 

[Aladin Akyurek]

[For weight lookup]=[@material]&" "&[@[thickness '[mm']]]
and as a value, the first row is
[For weight lookup]=E 6
the second
[For weight lookup]=EA2 4

Would you trying the following?

1.

=VLOOKUP(TRIM([For weight lookup]);'AAA\[weight.xlsx]Weight'!$B$2:$C$27;2;FALSE)

2. If the foregoing does not help...

Select the formula cells.
Activate Find & Select | Replace
Set Find What to: =
Set Replace With to: =
Activate the Replace All button

Does this help?

 

[gmanos]

Thanks but both suggestions didn't work.
Still after opening and updating the excel file, double click and enter needed on every cell of this row to work properly.
Maybe it is an excel's bug (?)
But anyway thanks for the help.

 

[gmanos]

*column, not row

 

[Aladin Akyurek]

Thanks but both suggestions didn't work.
Still after opening and updating the excel file, double click and enter needed on every cell of this row to work properly.
Maybe it is an excel's bug (?)
But anyway thanks for the help.

That's too soon to decide to that.

Open the file.

Modify the formula bit to the following:
=VLOOKUP([For weight lookup];'AAA\[weight.xlsx]Weight'!$B$2:$C$6;2;FALSE)

Select the ([For weight lookup] bit on the formula bar, hit F9.

Copy the F9 result and hit Escape key. Paste the copied result bit here.

Go back. Do the same with the 'AAA\[weight.xlsx]Weight'!$B$2:$C$6.

 

[gmanos]

changed to $C$6
[For weight lookup] =
E 6

Go back. Do the same with the 'AAA\[weight.xlsx]Weight'!$B$2:$C$6.

What do you mean?

 

[Aladin Akyurek]

changed to $C$6
[For weight lookup] =
E 6


What do you mean?

The same thing as what we did for the first part: Select, F9, Copy, Escape, and Paste here.

 

[gmanos]

First row, at the problematic column:
=VLOOKUP([For weight lookup];'AAA\[weight.xlsx]Weight'!$B$2:$C$6;2;FALSE)

when i do F9:
#REF!
After i hit escape the correct calculated value is at the cell (355.2)

 

[Aladin Akyurek]

First row, at the problematic column:
=VLOOKUP([For weight lookup];'AAA\[weight.xlsx]Weight'!$B$2:$C$6;2;FALSE)

when i do F9:
#REF!
After i hit escape the correct calculated value is at the cell (355.2)

My bad. Open weight.xlsx. Select B2:C6 in the weight sheet, hit F9, copy the result you see, escape, then paste the copied bit here.

 

[gmanos]

weight excel, weight sheet, B2:C27
[TABLE="width: 137"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Material[/TD]
[TD]w/m2[/TD]
[/TR]
[TR]
[TD]E 3[/TD]
[TD="align: right"]4.6[/TD]
[/TR]
[TR]
[TD]E 4[/TD]
[TD="align: right"]5.5[/TD]
[/TR]
[TR]
[TD]E 6[/TD]
[TD="align: right"]7.4[/TD]
[/TR]
[TR]
[TD]EFR 3[/TD]
[TD="align: right"]5.8[/TD]
[/TR]
[TR]
[TD]EFR 4[/TD]
[TD="align: right"]7.4[/TD]
[/TR]
[TR]
[TD]EFR 6[/TD]
[TD="align: right"]10.5[/TD]
[/TR]
[TR]
[TD]EA2 3[/TD]
[TD="align: right"]5.8[/TD]
[/TR]
[TR]
[TD]EA2 4[/TD]
[TD="align: right"]7.4[/TD]
[/TR]
[TR]
[TD]EA2 6[/TD]
[TD="align: right"]10.5[/TD]
[/TR]
[TR]
[TD]ESL 2[/TD]
[TD="align: right"]2.6[/TD]
[/TR]
[TR]
[TD]ESL 3[/TD]
[TD="align: right"]3.5[/TD]
[/TR]
[TR]
[TD]ESL 4[/TD]
[TD="align: right"]4.4[/TD]
[/TR]
[TR]
[TD]ESL 6[/TD]
[TD="align: right"]6.2[/TD]
[/TR]
[TR]
[TD]E d1 2[/TD]
[TD="align: right"]2.4[/TD]
[/TR]
[TR]
[TD]E d1 3[/TD]
[TD="align: right"]3.3[/TD]
[/TR]
[TR]
[TD]E d1 4[/TD]
[TD="align: right"]4.3[/TD]
[/TR]
[TR]
[TD]E d1 6[/TD]
[TD="align: right"]6.1[/TD]
[/TR]
[TR]
[TD]EL 2[/TD]
[TD="align: right"]2.9[/TD]
[/TR]
[TR]
[TD]EL 3[/TD]
[TD="align: right"]3.8[/TD]
[/TR]
[TR]
[TD]EL 4[/TD]
[TD="align: right"]4.8[/TD]
[/TR]
[TR]
[TD]EL 6[/TD]
[TD="align: right"]6.6[/TD]
[/TR]
[TR]
[TD]ELFR 2[/TD]
[TD="align: right"]3.69[/TD]
[/TR]
[TR]
[TD]ELFR 3[/TD]
[TD="align: right"]5.24[/TD]
[/TR]
[TR]
[TD]ELFR 4[/TD]
[TD="align: right"]6.79[/TD]
[/TR]
[TR]
[TD]ELFR 6[/TD]
[TD="align: right"]9.89[/TD]
[/TR]
</tbody>[/TABLE]

Those values are not calculated (there are no formulas behind).