Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
R1C1 formula question | Excel Worksheet Functions | |||
Addition string from input box in to a formula (eg;R1C1 Formula) | Excel Programming | |||
R1C1 to A1 inaccurate formula conversion | Excel Programming | |||
R1C1 Formula in Excel | Excel Worksheet Functions | |||
Variable in an R1C1 formula | Excel Programming |