| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 87006 | 2008-02-04 10:57:00 | Excel questions | bpt2 (6653) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 637351 | 2008-02-04 10:57:00 | I want to create a a column of four digit numbers (account codes) some of which begin with "0". Can I convert 901 to 0901 or do I have to write them all in as text? When I reference a cell from another one why do I sometimes get the reference (='Sheet2'!A3) rather than the content of that cell? Sometimes a calculation is not updated when I have set the option under tools to automatically update. |
bpt2 (6653) | ||
| 637352 | 2008-02-04 11:19:00 | I want to create a a column of four digit numbers (account codes) some of which begin with "0". Can I convert 901 to 0901 or do I have to write them all in as text? When I reference a cell from another one why do I sometimes get the reference (='Sheet2'!A3) rather than the content of that cell? Sometimes a calculation is not updated when I have set the option under tools to automatically update. You may have a spreadsheet which was created by another person. It appears to me that you could be out of your depth. This after reading other posts you have made. You have asked two questions in this post. Without seeing the actual spreadsheet I can not answer the question. Did you design this or do you want to alter a file made by another person? Creating a column is easy. |
Sweep (90) | ||
| 637353 | 2008-02-04 13:12:00 | I have exported the codes from an accounting package and the "0" drops off when the original ledger code started with a "0" as in 0901. I have no problem writing the codes in text but I just want to be able to import them and keep or add the zero. This is needed for me to export the same file back after I have done some work on the file in excel. |
bpt2 (6653) | ||
| 637354 | 2008-02-04 19:31:00 | Hi bpt2 I have this problem with exporting from our G/L here too; you could try something along the lines of this (Codes in column A): =IF(LEN(A1)=3,CONCATENATE("0",A1),A1) This evaluates the length of the code, and appends a leading zero if the length is 3 digits . Excel is notorious for stripping leading zero's . . . . If you have to import back into your accounting package, you'll probably have to create a . csv file If you're seeing (='Sheet2'!A3), it's possibly because you're referencing that cell from a different sheet in your workbook (i . e . not Sheet 2) HTH Chris |
nofam (9009) | ||
| 637355 | 2008-02-04 23:00:00 | For the 1st bit, you could format that column as Custom & enter 0000 as the Type field - that way the number is still a number, just displayed with a minimum of 4 digits | MushHead (10626) | ||
| 637356 | 2008-02-05 00:04:00 | If is an issue of display numbers with leading zeros, then Mushhead's solution of using a custom format type is the way to go . This has the advantage of retaining the value as a number instead of text string, which may be useful if you have to do further calculations . If instead you want the equivalent text string use the formula =TEXT(A1,"0000") where in this example the cell reference is A1 . You second question re excel sometimes display the formula in the sheet as opposed to evaluating it is a display option within Excel . To preview formulas you can go to Tools -> Options -> View tab and then select the formulas checkbox under window options . The quick way is to use the shortcut keys "Ctl + ~" . Do the reverse and it will revert back to evaluating formulas . Lastly, getting formulas to evaluate go to Tools -> Options -> Calculation and you have a couple of options from automatic to manual . Note that hitting F9 when in manual mode will automatically refresh the calculations within a spreadsheet . HTH Dave |
odyssey (4613) | ||
| 637357 | 2008-02-05 09:19:00 | Thanks for the help. | bpt2 (6653) | ||
| 1 | |||||