Formula shows in cell instead of results (text reference)
in [General]
|
Prev: auto update to multiple worksheets
Next: Working hours
From: Dave on 5 Apr 2010 18:06 I have a file with 1,000's of forumlas in it. One tab alone has over 3,400 formulas. There are 20 tabs that pull a job category name from the "rates" tab. For year 1, the formulas start in A9 and go to A63. I have multiple years that deal with the same job categories and the information for Years 2 - 5 starts on row 106 and continue down. If I put a formula in A106 that says + or = A9, the cell displays the forumla and not the job category name. Cell A106 is formatted as text which it should be. I have tried the edit format and change A106 to text then hit F2 then Enter. This does not fix the problem. I am using Excel 2003. What is causing the problem? Any help is appreciated. This has been a problem that has been going on for years but no one has ever taken them time to attempt to diagnose. Thanks
From: Paul on 5 Apr 2010 18:21 When you format a cell as Text, anything you put into that cell will be stored literally as text, not as a formula. You need to format the cell(s) back to General (or Date, or Number, etc.). Even after you re-format the cells they may not automatically update to the formula results, in which case you need to "help" them convert to the new format. Easiest way with 3400+ formula cells would be to select them and do a Find/Replace. Find what: = Replace with: = Do a 'Replace All' and you should be set. D a v e ; 6 9 0 9 8 5 W r o t e : > I have a file with 1,000's of forumlas in it. One tab alone has over 3,400 > formulas. > > There are 20 tabs that pull a job category name from the "rates" tab. For > year 1, the formulas start in A9 and go to A63. I have multiple years that > deal with the same job categories and the information for Years 2 - 5 starts > on row 106 and continue down. If I put a formula in A106 that says + or = > A9, the cell displays the forumla and not the job category name. Cell A106 > is formatted as text which it should be. I have tried the edit format and > change A106 to text then hit F2 then Enter. This does not fix the problem. > > I am using Excel 2003. What is causing the problem? > > Any help is appreciated. This has been a problem that has been going on for > years but no one has ever taken them time to attempt to diagnose. > > Thanks -- Paul - Paul ------------------------------------------------------------------------ Paul's Profile: 1697 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=193168 http://www.thecodecage.com/forumz
From: RagDyer on 5 Apr 2010 18:24 <<<"Cell A106 is formatted as text which it should be.>>>" Since A106 contains the formula: =A9 it *SHOULD NOT* be formatted as text. Format A106 to either General or Number and your problem should be solved. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dave" <Dave(a)discussions.microsoft.com> wrote in message news:6D596419-39A5-465E-BF0D-39932E300CC6(a)microsoft.com... >I have a file with 1,000's of forumlas in it. One tab alone has over 3,400 > formulas. > > There are 20 tabs that pull a job category name from the "rates" tab. For > year 1, the formulas start in A9 and go to A63. I have multiple years > that > deal with the same job categories and the information for Years 2 - 5 > starts > on row 106 and continue down. If I put a formula in A106 that says + or = > A9, the cell displays the forumla and not the job category name. Cell > A106 > is formatted as text which it should be. I have tried the edit format and > change A106 to text then hit F2 then Enter. This does not fix the > problem. > > I am using Excel 2003. What is causing the problem? > > Any help is appreciated. This has been a problem that has been going on > for > years but no one has ever taken them time to attempt to diagnose. > > Thanks
From: tompl on 5 Apr 2010 18:27 Entering a formula in a cell that is formated text gives you test, not a formula. Try formating A106 as general then reenter the formula and see if that works. Tom
From: Dave on 5 Apr 2010 19:36
tompl, Paul, and Ragdyer, thanks for the help. Your input fixed a years old problem. "RagDyer" wrote: > <<<"Cell A106 is formatted as text which it should be.>>>" > > Since A106 contains the formula: > =A9 > it *SHOULD NOT* be formatted as text. > > Format A106 to either General or Number and your problem should be solved. > -- > HTH, > > RD > > --------------------------------------------------------------------------- > Please keep all correspondence within the NewsGroup, so all may benefit ! > --------------------------------------------------------------------------- > "Dave" <Dave(a)discussions.microsoft.com> wrote in message > news:6D596419-39A5-465E-BF0D-39932E300CC6(a)microsoft.com... > >I have a file with 1,000's of forumlas in it. One tab alone has over 3,400 > > formulas. > > > > There are 20 tabs that pull a job category name from the "rates" tab. For > > year 1, the formulas start in A9 and go to A63. I have multiple years > > that > > deal with the same job categories and the information for Years 2 - 5 > > starts > > on row 106 and continue down. If I put a formula in A106 that says + or = > > A9, the cell displays the forumla and not the job category name. Cell > > A106 > > is formatted as text which it should be. I have tried the edit format and > > change A106 to text then hit F2 then Enter. This does not fix the > > problem. > > > > I am using Excel 2003. What is causing the problem? > > > > Any help is appreciated. This has been a problem that has been going on > > for > > years but no one has ever taken them time to attempt to diagnose. > > > > Thanks > > > . > |