 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Charting / November 2006Formating textbox on a chart
I have a dollar amount created with the formula =IF(NewProfit<0,"$"&FIXED(-NewProfit,-2),"$"&FIXED(NewProfit,-2)) in an Excel spreadsheet which is linked to a textbox on a chart. I would like to format the numbers in the chart 1) Bold and 2) Blue when positive and Red when negative. Any suggestion would be greatly appreciated, Roger You can just select the textbox and select Bold. You don't need the IF to get conditional formatting or your dollar format. The cell just needs this formula =ROUND(NewProfit,-2) (FIXED results in text, not a number) and a custom number format of [blue]$#,##0;[red]$-#,##0;$0;@ Link the textbox to the cell, and apply the same number format to the textbox. More on number formats: http://peltiertech.com/Excel/NumberFormats.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ >I have a dollar amount created with the formula >=IF(NewProfit<0,"$"&FIXED(-NewProfit,-2),"$"&FIXED(NewProfit,-2)) in an [quoted text clipped - 6 lines] > > Roger Jon, thanks so much for your update. It was very helpful. There is only one problem: I can't get the "Format Cells, Number" when working in the TextBox. Instead I get "Format TextBox" but can't find the numbers. Also looked at your website but couldn't find the cure. Any suggestions would be very helpful. Thanks, Roger > You can just select the textbox and select Bold. > [quoted text clipped - 31 lines] >> >> Roger Hi, I think maybe Jon was think of a data label, which does have a number format. For textboxes I think you will need to use 2 of everything. 2 formula to display the result positive =IF(ROUND(NewProfit,-2)>=0,ROUND(NewProfit,-2),"") negative =IF(ROUND(NewProfit,-2)<0,ROUND(NewProfit,-2),"") and 2 textboxes with the correct formatting applied. Cheers Andy > Jon, thanks so much for your update. It was very helpful. There is only > one problem: I can't get the "Format Cells, Number" when working in the [quoted text clipped - 39 lines] >>> >>>Roger  SignatureAndy Pope, Microsoft MVP - Excel http://www.andypope.info Sorry. I was thinking of textboxes, but I wasn't testing, just typing. Otherwise I'd have noticed the lack of number formatting in a text box. My Bad. Jon Peltier, Microsoft Excel MVP http://PeltierTech.com > Hi, > [quoted text clipped - 57 lines] >>>> >>>>Roger Jon, am not sure of your conclusion. Is there any way of connecting the Textbox to the spreadsheet with the colours showing in the Textbox? Thanks, Roger > Sorry. I was thinking of textboxes, but I wasn't testing, just typing. > Otherwise I'd have noticed the lack of number formatting in a text box. [quoted text clipped - 64 lines] >>>>> >>>>>Roger Here's what I did. I formatted the cell with the custom number format. I copied the cell (C8 on Sheet1), selected the chart, held Shift while selecting the Edit menu, and chose Paste Picture. I selected the picture, clicked in the Formula bar, and typed =Sheet1!$C$8, a link to the cell. Now the picture of the cell is dynamic, including the value and the format. I don't know how stable this is. I seem to recall crashes using this kind of technique in earlier versions of Excel (I'm using 2003). - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ > Jon, am not sure of your conclusion. Is there any way of connecting the > Textbox to the spreadsheet with the colours showing in the Textbox? [quoted text clipped - 69 lines] >>>>>> >>>>>>Roger Jon, thanks for the great instructions. It all works except that I can't get the size of the picture to adjust to the size I want. I have also seen a crash but that was caused by moving things around so hopefully it will stay OK when not moved about. I really appreciate your detailed reply, Roger > Here's what I did. I formatted the cell with the custom number format. I > copied the cell (C8 on Sheet1), selected the chart, held Shift while [quoted text clipped - 84 lines] >>>>>>> >>>>>>>Roger Roger - Email me in a month and let me know if it still seems sufficiently stable. It does seem less shaky in 2003 than in 2000, when I recall having some nice crashes using this technique. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ > Jon, thanks for the great instructions. It all works except that I can't > get the size of the picture to adjust to the size I want. I have also [quoted text clipped - 94 lines] >>>>>>>> >>>>>>>>Roger Will do, Roger > Roger - > [quoted text clipped - 108 lines] >>>>>>>>> >>>>>>>>>Roger Hi Jon, I lost my msnews for several months so could not answer your question sooner. I got the "Formating textbox on a chart" to work initially and it has worked really well since then. Many thanks for all your kind help. I'll send this to you personally as well as on the newsgroup we were using. Roger > Will do, > [quoted text clipped - 113 lines] >>>>>>>>>> >>>>>>>>>>Roger |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.