Formatting a cell

[mac]

I am doing a vlookup in a template that has several year sheet. The common
lookup for this template is in column A and is set up as text ex(0834563)but
when I do the lookup I get na for some of the years and some work okay. I
have tried to set up the column as general but I does not seem to work. Any
suggestions as to when I put in the lookup I get all data for the years I
want? Any help is greatly appreciated.

 

[Spiky]

I am doing a vlookup in a template that has several year sheet. The common
lookup for this template is in column A and is set up as text ex(0834563)but
when I do the lookup I get na for some of the years and some work okay. I
have tried to set up the column as general but I does not seem to work. Any
suggestions as to when I put in the lookup I get all data for the years I
want? Any help is greatly appreciated.

Are you absolutely sure the lookup value matches the items in the
first column of the database? No spaces, leading zeroes, letter O
instead of numeral 0, or other typo issues.

 

[mac]

Hello Spiky,

Yes, I have checked every column and the all have general as the format. If
I re-enter the cell it fixes it, but I did not want to rekey all the cells
(250 on each sheet).


thank you mac

 

[ward376]

If they work after re-entry, then they didn't match prior to re-
entry.

Cell formatting doesn't change the underlying data type - cells
formatted as general can have any data type in them, only the
appearance changes.

If there isn't a compelling reason to format as text, you can convert
the lookup ranges to values which should get your lookups hooked up.
Example:
Sub vert()
With Worksheets("Sheet1").Columns("A")
.NumberFormat = "General"
.Value = .Value
End With
End Sub

To "re-enter" data, you don't have to type it back into the cell -
just select the cell, hit f2 then enter.

Cliff Edwards

 

[Spiky]

Hello Spiky,

Yes, I have checked every column and the all have general as the format. If
I re-enter the cell it fixes it, but I did not want to rekey all the cells
(250 on each sheet).

thank you mac

Yeah, I didn't mean formats. I meant the actual text/value in the
cell. I always find it best to copy when using Lookups so that I don't
make typos and screw up my calculations. So, enter the Lookup column
data somewhere, then always copy from that to assure 100% matching.

 

[Spiky]

If they work after re-entry, then they didn't match prior to re-
entry.

To "re-enter" data, you don't have to type it back into the cell -
just select the cell, hit f2 then enter.

Cliff Edwards

I just wanted to point out that these two comments do not work
together. If "re-entry" fixes a lookup problem because of a typo, then
F2 and Enter are not "re-entry".

 

[ward376]

You're right, that wasn't very clear. They weren't meant to work
together...

If they work after re-entry, then they didn't match prior to re-
entry.

Re-entering the same info into a cell will prompt Excel to convert the
data into the data type and format it thinks it is, unless the cell is
formatted as text. So re-entering the data may change the data type.

To "re-enter" data, you don't have to type it back into the cell -
just select the cell, hit f2 then enter.

This is a tip, just making sure you knew you didn't have to type every
character into cells, you can just enter edit mode with f2 to "re-
enter" if the issue is the data types not matching between the lookup
value and the lookup table. Of course typos are an entirely different
matter.

Cliff Edwards