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

 

[Alpacino]

Check if calculation is on manual or automatic under options.

 

[bdunk]

It is set to Automatic.

 

[T. Valko]

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

It sounds like you may have changed the cell format but that format is not applied until you edit the cell. When you select the cell and do this:

In the Formula Bar above I place my cursor at the end of the number and press the Enter key.

You are editing the cell at which time the new format will be applied.

See this:

http://contextures.com/xlFunctions02.html#Trouble

 

[gmanos]

It sounds like you may have changed the cell format but that format is not applied until you edit the cell. When you select the cell and do this:


You are editing the cell at which time the new format will be applied.

See this:

http://contextures.com/xlFunctions02.html#Trouble

I have the same problem but the article did't help. Any suggestion?

Thanks

 

[Aladin Akyurek]

@gmanos

Are you seeing the formula instead of a result in the formula cell?

 

[gmanos]

No, i see a #N/A error.

 

[Aladin Akyurek]

No, i see a #N/A error.

Unjustified? Would you post the formula?

 

[gmanos]

Every time i open this excel, the values of a specific column of the table are turn to #N/A and i have to click and enter on each cell to fix. Refresh didn't help.

the formula
=VLOOKUP([For weight lookup];'AAA\[weight.xlsx]Weight'!$B$2:$C$27;2;FALSE)*[@[Area '[m2']]]
where AAA is a network location.

The formula is correct as when i do click and enter, the correct data are shown.

I used to have this excel file at another location on the HDD and the same problem was present but in another column (!?). the formula of the latter column is
=VLOOKUP([@[For date, month]];'AAA\[weight.xlsx]Month'!$B$2:$C$27;2;FALSE)
where AAA is a network location.

 

[Aladin Akyurek]

Every time i open this excel, the values of a specific column of the table are turn to #N/A and i have to click and enter on each cell to fix. Refresh didn't help.

the formula
=VLOOKUP([For weight lookup];'AAA\[weight.xlsx]Weight'!$B$2:$C$27;2;FALSE)*[@[Area '[m2']]]
where AAA is a network location.

The formula is correct as when i do click and enter, the correct data are shown.

I used to have this excel file at another location on the HDD and the same problem was present but in another column (!?). the formula of the latter column is
=VLOOKUP([@[For date, month]];'AAA\[weight.xlsx]Month'!$B$2:$C$27;2;FALSE)
where AAA is a network location.

What is the value of [For weight lookup] exactly?