Formula R1C1
 

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!

Formula R1C1
 

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

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

kay wrote:

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

Formula R1C1
 

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" wrote:

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

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

kay wrote:

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

Formula R1C1
 

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.

kay wrote:

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" wrote:

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

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

kay wrote:

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


--

Dave Peterson