Formula R1C1

[kay]

I have a column and it has number value(geneal) for eg in cell T2 -
9456852, in
other sheet it has category as well for eg- in cell F2 - 9456852 , cell I2
= Cancelled

So when i do Vlookup it does not put Cancelled next to 9456852 , but when i
double click on cell T2 mean double click on 9456852 then it gives me value
"Cancelled".

So i figured out that is Formula R1C1 is running when i double click.
there is a formula Activecell.FormulaR1C1 = "9456852'
but i have 10000 rows and each row has different values and it hard to do
(manuaaly double clikc on each cell) for each cell,

So is there a way that we can do this for whole T column so then my vlookup
will work.

Thanks!

 

[Dave Peterson]

It kind of sounds like you have excel in manual calculation mode.

In xl2003 menus, you can check:
tools|Options|calculation tab

 

[kay]

No i dont have calculation in manual mode, it is in Automatic mode.
but the data i have in the spreadsheet is extracted from SAP.
and then i am doing vlooup and unless i click on that cell , vlookup us not
recognizing the value.

 

[Dave Peterson]

Changing the format of the cell from general to text or from text to general
doesn't change the value in the cell. It will change the value (from a string
to a number or vice versa) if you edit the cell (even F2|enter is enough).

You can change your =vlookup()'s to look for numbers if the table contains
numbers:

=vlookup(--a1,sheet2!a:b,2,false)

or
You can change your =vlookup()'s to look for text if the table contains text:

=vlookup(a1&"",sheet2!a:b,2,false)

But personally, I would change the value to what it's supposed to be--either a
real number or real text.

In your case, it sounds like the data should be numeric.

You can select an empty cell
Edit|copy
select the range of offending cells
Edit|paste special|click Add and values

And your text numbers will be converted to number numbers.