Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting won't apply to my cell with a formula
I downloaded the checkbook register from Microsoft.com and am trying to add a
few cells at the end to quickly calculate my outstanding debits and credits so that I can balance my statements automatically. I figured out how to write the formula properly (eventually), but the cells will not take on the currency format no matter what I do. I have tried pasting the format from the other cells within the register, tried clearing the format and reapplying the currency format, tried accounting, etc. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting won't apply to my cell with a formula
Adrienne,
Try removing and Conditional Formatting that may have been applied. HTH, Bernie MS Excel MVP "Adrienne" wrote in message ... I downloaded the checkbook register from Microsoft.com and am trying to add a few cells at the end to quickly calculate my outstanding debits and credits so that I can balance my statements automatically. I figured out how to write the formula properly (eventually), but the cells will not take on the currency format no matter what I do. I have tried pasting the format from the other cells within the register, tried clearing the format and reapplying the currency format, tried accounting, etc. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting won't apply to my cell with a formula
I searched and it seems there isn't any conditional formatting applied to the
cells. I tried clearing all formatting under the edit menu, and still when I try to format the cell with currency formatting, it doesn't take it. Even in the formatting window, the sample doesn't show it with the decimal points of the currency symbol as it does in the other cells in the other part of the register that were formatted that way in the template. Any other suggestions? "Bernie Deitrick" wrote: Adrienne, Try removing and Conditional Formatting that may have been applied. HTH, Bernie MS Excel MVP "Adrienne" wrote in message ... I downloaded the checkbook register from Microsoft.com and am trying to add a few cells at the end to quickly calculate my outstanding debits and credits so that I can balance my statements automatically. I figured out how to write the formula properly (eventually), but the cells will not take on the currency format no matter what I do. I have tried pasting the format from the other cells within the register, tried clearing the format and reapplying the currency format, tried accounting, etc. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting won't apply to my cell with a formula
Isn't your cell actually text, although it may look like a number?
Try =ISNUMBER(A1) If it is text,: Format an empty cell as General. Enter the number 1. EditCopy. Select your data. EditPaste Special, check Multiply. -- Kind regards, Niek Otten "Adrienne" wrote in message ... I downloaded the checkbook register from Microsoft.com and am trying to add a few cells at the end to quickly calculate my outstanding debits and credits so that I can balance my statements automatically. I figured out how to write the formula properly (eventually), but the cells will not take on the currency format no matter what I do. I have tried pasting the format from the other cells within the register, tried clearing the format and reapplying the currency format, tried accounting, etc. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting won't apply to my cell with a formula
Hi Adrienne,
Conditional Formatting has nothing to do with number formatting, don't think Bernie read the entire question -- I think the example might come with some Conditional Formatting for color banding, but is devoid of the important number formatting. If you use Format, cells, custom what do you see for the format of the active cell. I don't think the checkbook example has formatting, formulas, and if that is the case it certainly is not of much use as an example. You might find the following of interest after you figure out what the problem is with the formats. http://www.mvps.org/dmcritchie/excel/insrtrow.htm It is an example of a checkbook. You might check your data, first of all if the numbers are left justified that is a pretty sure indication that they are not numbers. =ISTEXT(C4) or the opposite =ISNUMBER(C4) After you change a format between text and number in either direction, you must reenter it see it this works. Its not a matter of what you say the format is, it's a matter of what Excel was using when the data was originally entered before changing a format. F2 then Enter http://www.mvps.org/dmcritchie/excel/join.htm#trimall and read about how to find out what you actually have. To see formulas and formats assigned to another cell see GetFormula and GetFormat macros in http://www.mvps.org/dmcritchie/excel/formula.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Adrienne, Try removing and Conditional Formatting that may have been applied. HTH, Bernie MS Excel MVP "Adrienne" wrote in message ... I downloaded the checkbook register from Microsoft.com and am trying to add a few cells at the end to quickly calculate my outstanding debits and credits so that I can balance my statements automatically. I figured out how to write the formula properly (eventually), but the cells will not take on the currency format no matter what I do. I have tried pasting the format from the other cells within the register, tried clearing the format and reapplying the currency format, tried accounting, etc. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting won't apply to my cell with a formula
I think you're both right that it is being formatted as text, because it is
left-justified. Although this seems weird, since it is a formula for adding and subtracting - shouldn't the computer know that's a number? Let me clarify exactly what I've done and see if you can give me any more specific pointers: I downloaded the check register from microsoft's web page. It has the standard register columns, and I use the one labeled c to mark things that have cleared at the bank with an x. Then there is a column of debits and one of credits. So at the bottom, I created a formula to add up the debits not yet cleared and another to add up the credits not yet cleared so that I can balance my bank statement (basically a recreation of the form that's on the back of your bank statement). Here's a copy of the cells I added at the bottom and the formulas (also note how the numbers are formatted in the last 3 rows, the ones with the formulas): Statement Ending Balance: $1,645.92 Outstanding Debits: 3982.53 Outstanding Credits: 2476.9 Account Register Balance? 140.29 Formula for outstanding debits: =IMSUB(SUM(F6:F499),SUMIF(E6:E499,"x",F6:F499)) Formula for outstanding credits:=IMSUB(SUM(G6:G499),SUMIF(E6:E499,"x",G6:G 499)) And the formula for account register balance: =IMSUB(H501+G503,F502) If I do need to add the =ISNUMBER command, I'm not sure how to do that when these formulas are already there. Thanks for working through this with me! adrienne "Niek Otten" wrote: Isn't your cell actually text, although it may look like a number? Try =ISNUMBER(A1) If it is text,: Format an empty cell as General. Enter the number 1. EditCopy. Select your data. EditPaste Special, check Multiply. -- Kind regards, Niek Otten "Adrienne" wrote in message ... I downloaded the checkbook register from Microsoft.com and am trying to add a few cells at the end to quickly calculate my outstanding debits and credits so that I can balance my statements automatically. I figured out how to write the formula properly (eventually), but the cells will not take on the currency format no matter what I do. I have tried pasting the format from the other cells within the register, tried clearing the format and reapplying the currency format, tried accounting, etc. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting won't apply to my cell with a formula
Oh, and to answer your question. Format, cells, custom shows this:
$#,##0.00 );($#,##0.00) "David McRitchie" wrote: Hi Adrienne, Conditional Formatting has nothing to do with number formatting, don't think Bernie read the entire question -- I think the example might come with some Conditional Formatting for color banding, but is devoid of the important number formatting. If you use Format, cells, custom what do you see for the format of the active cell. I don't think the checkbook example has formatting, formulas, and if that is the case it certainly is not of much use as an example. You might find the following of interest after you figure out what the problem is with the formats. http://www.mvps.org/dmcritchie/excel/insrtrow.htm It is an example of a checkbook. You might check your data, first of all if the numbers are left justified that is a pretty sure indication that they are not numbers. =ISTEXT(C4) or the opposite =ISNUMBER(C4) After you change a format between text and number in either direction, you must reenter it see it this works. Its not a matter of what you say the format is, it's a matter of what Excel was using when the data was originally entered before changing a format. F2 then Enter http://www.mvps.org/dmcritchie/excel/join.htm#trimall and read about how to find out what you actually have. To see formulas and formats assigned to another cell see GetFormula and GetFormat macros in http://www.mvps.org/dmcritchie/excel/formula.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Adrienne, Try removing and Conditional Formatting that may have been applied. HTH, Bernie MS Excel MVP "Adrienne" wrote in message ... I downloaded the checkbook register from Microsoft.com and am trying to add a few cells at the end to quickly calculate my outstanding debits and credits so that I can balance my statements automatically. I figured out how to write the formula properly (eventually), but the cells will not take on the currency format no matter what I do. I have tried pasting the format from the other cells within the register, tried clearing the format and reapplying the currency format, tried accounting, etc. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting won't apply to my cell with a formula
Sorry - I read this again more thoroughly after I realized I didn't have to
use the ISNUMBER -the paste special, multiple works! And I can copy the formatting from the other cells using the paint function and they remain as currency. Thanks! adrienne "Niek Otten" wrote: Isn't your cell actually text, although it may look like a number? Try =ISNUMBER(A1) If it is text,: Format an empty cell as General. Enter the number 1. EditCopy. Select your data. EditPaste Special, check Multiply. -- Kind regards, Niek Otten "Adrienne" wrote in message ... I downloaded the checkbook register from Microsoft.com and am trying to add a few cells at the end to quickly calculate my outstanding debits and credits so that I can balance my statements automatically. I figured out how to write the formula properly (eventually), but the cells will not take on the currency format no matter what I do. I have tried pasting the format from the other cells within the register, tried clearing the format and reapplying the currency format, tried accounting, etc. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting won't apply to my cell with a formula
Adrienne
Is this the Checkbook Register Template you downloaded? http://office.microsoft.com/en-us/te...CT011377171033 If so, just select B89:H89 and drag/copy down as far as you wish. There is no CF on any of the cells. Just the Currency formatting. Gord Dibben Excel MVP On Sun, 27 Nov 2005 07:45:03 -0800, "Adrienne" wrote: I searched and it seems there isn't any conditional formatting applied to the cells. I tried clearing all formatting under the edit menu, and still when I try to format the cell with currency formatting, it doesn't take it. Even in the formatting window, the sample doesn't show it with the decimal points of the currency symbol as it does in the other cells in the other part of the register that were formatted that way in the template. Any other suggestions? "Bernie Deitrick" wrote: Adrienne, Try removing and Conditional Formatting that may have been applied. HTH, Bernie MS Excel MVP "Adrienne" wrote in message ... I downloaded the checkbook register from Microsoft.com and am trying to add a few cells at the end to quickly calculate my outstanding debits and credits so that I can balance my statements automatically. I figured out how to write the formula properly (eventually), but the cells will not take on the currency format no matter what I do. I have tried pasting the format from the other cells within the register, tried clearing the format and reapplying the currency format, tried accounting, etc. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting won't apply to my cell with a formula
Hi Adrienne,
I hadn't even noticed Niek's reply, but it would be better to select an empty cell to copy and then use Special Cells Add so that you do not create zeros where you had empty cells. Especially if you are using a Debit column and a Credit column. But much easier is to have installed the TrimAll macro and it will remove the spaces and/or Char(160) characters. Test first if dates are involved, but I have it installed so I can access from a menu and it really helps when working with data downloaded from the internet. Of course if you are downloading your data from the Bank, you might consider using Quicken Deluxe (Deluxe gives you internet access), or since this is a Microsoft group -- MS Money, and updating will be a lot easier, no need to do a lot of futzing around in Excel. . For one bank account it might not make much difference, but if you ever invest in stocks you will need a package like Quicken to keep records, and history of everything. ... --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Adrienne" wrote in message ... Sorry - I read this again more thoroughly after I realized I didn't have to use the ISNUMBER -the paste special, multiple works! And I can copy the formatting from the other cells using the paint function and they remain as currency. Thanks! adrienne "Niek Otten" wrote: Isn't your cell actually text, although it may look like a number? Try =ISNUMBER(A1) If it is text,: Format an empty cell as General. Enter the number 1. EditCopy. Select your data. EditPaste Special, check Multiply. -- Kind regards, Niek Otten "Adrienne" wrote in message ... I downloaded the checkbook register from Microsoft.com and am trying to add a few cells at the end to quickly calculate my outstanding debits and credits so that I can balance my statements automatically. I figured out how to write the formula properly (eventually), but the cells will not take on the currency format no matter what I do. I have tried pasting the format from the other cells within the register, tried clearing the format and reapplying the currency format, tried accounting, etc. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set a cell value based on a formula in another cell? | Excel Discussion (Misc queries) | |||
How does special formula apply to conditional formatting? | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |