Main Page 
PC Review
Forums
Newsgroups
Microsoft Excel
Microsoft Excel Charting
Formating textbox on a chart
Forums
Newsgroups
Microsoft Excel
Microsoft Excel Charting
Formating textbox on a chart
 |
Formating textbox on a chart |
|
|
Thread Tools | Rate Thread |
14-09-2006, 08:08 PM
|
#1 |
|
Guest
Posts: n/a
|
Formating 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 |
|
|
15-09-2006, 04:13 AM
|
#2 |
|
Guest
Posts: n/a
|
Re: Formating textbox on a chart
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 _______ "Roger B." <rb10@canada.com> wrote in message news:%23v7csED2GHA.3656@TK2MSFTNGP04.phx.gbl... >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 > > |
|
|
|
15-09-2006, 07:00 AM
|
#3 |
|
Guest
Posts: n/a
|
Re: Formating textbox on a chart
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 "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message news:uJ%236BUH2GHA.4264@TK2MSFTNGP05.phx.gbl... > 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 > _______ > > > "Roger B." <rb10@canada.com> wrote in message > news:%23v7csED2GHA.3656@TK2MSFTNGP04.phx.gbl... >>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 >> >> > > |
|
|
|
15-09-2006, 09:02 AM
|
#4 |
|
Guest
Posts: n/a
|
Re: Formating textbox on a chart
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 Roger B. wrote: > 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 > > > "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message > news:uJ%236BUH2GHA.4264@TK2MSFTNGP05.phx.gbl... > >>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 >>_______ >> >> >>"Roger B." <rb10@canada.com> wrote in message >>news:%23v7csED2GHA.3656@TK2MSFTNGP04.phx.gbl... >> >>>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 >>> >>> >> >> > > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
|
|
|
15-09-2006, 11:34 PM
|
#5 |
|
Guest
Posts: n/a
|
Re: Formating textbox on a chart
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 "Andy Pope" <andy@andypope.info> wrote in message news:eBV8I1J2GHA.1040@TK2MSFTNGP06.phx.gbl... > 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 > > Roger B. wrote: >> 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 >> >> >> "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message >> news:uJ%236BUH2GHA.4264@TK2MSFTNGP05.phx.gbl... >> >>>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 >>>_______ >>> >>> >>>"Roger B." <rb10@canada.com> wrote in message >>>news:%23v7csED2GHA.3656@TK2MSFTNGP04.phx.gbl... >>> >>>>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 >>>> >>>> >>> >>> >> >> > > -- > > Andy Pope, Microsoft MVP - Excel > http://www.andypope.info |
|
|
|
16-09-2006, 03:40 AM
|
#6 |
|
Guest
Posts: n/a
|
Re: Formating textbox on a chart
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 "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message news:epQcXcR2GHA.3464@TK2MSFTNGP03.phx.gbl... > 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 > > > "Andy Pope" <andy@andypope.info> wrote in message > news:eBV8I1J2GHA.1040@TK2MSFTNGP06.phx.gbl... >> 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 >> >> Roger B. wrote: >>> 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 >>> >>> >>> "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message >>> news:uJ%236BUH2GHA.4264@TK2MSFTNGP05.phx.gbl... >>> >>>>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 >>>>_______ >>>> >>>> >>>>"Roger B." <rb10@canada.com> wrote in message >>>>news:%23v7csED2GHA.3656@TK2MSFTNGP04.phx.gbl... >>>> >>>>>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 >>>>> >>>>> >>>> >>>> >>> >>> >> >> -- >> >> Andy Pope, Microsoft MVP - Excel >> http://www.andypope.info > > |
|
|
|
17-09-2006, 05:24 AM
|
#7 |
|
Guest
Posts: n/a
|
Re: Formating textbox on a chart
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 _______ "Roger B." <rb10@canada.com> wrote in message news:uSbHslT2GHA.3428@TK2MSFTNGP05.phx.gbl... > 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 > > > "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message > news:epQcXcR2GHA.3464@TK2MSFTNGP03.phx.gbl... >> 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 >> >> >> "Andy Pope" <andy@andypope.info> wrote in message >> news:eBV8I1J2GHA.1040@TK2MSFTNGP06.phx.gbl... >>> 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 >>> >>> Roger B. wrote: >>>> 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 >>>> >>>> >>>> "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message >>>> news:uJ%236BUH2GHA.4264@TK2MSFTNGP05.phx.gbl... >>>> >>>>>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 >>>>>_______ >>>>> >>>>> >>>>>"Roger B." <rb10@canada.com> wrote in message >>>>>news:%23v7csED2GHA.3656@TK2MSFTNGP04.phx.gbl... >>>>> >>>>>>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 >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> -- >>> >>> Andy Pope, Microsoft MVP - Excel >>> http://www.andypope.info >> >> > > |
|
|
|
17-09-2006, 08:11 PM
|
#8 |
|
Guest
Posts: n/a
|
Re: Formating textbox on a chart
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 "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message news:O2d6xEh2GHA.3656@TK2MSFTNGP04.phx.gbl... > 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 > _______ > > > "Roger B." <rb10@canada.com> wrote in message > news:uSbHslT2GHA.3428@TK2MSFTNGP05.phx.gbl... >> 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 >> >> >> "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message >> news:epQcXcR2GHA.3464@TK2MSFTNGP03.phx.gbl... >>> 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 >>> >>> >>> "Andy Pope" <andy@andypope.info> wrote in message >>> news:eBV8I1J2GHA.1040@TK2MSFTNGP06.phx.gbl... >>>> 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 >>>> >>>> Roger B. wrote: >>>>> 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 >>>>> >>>>> >>>>> "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message >>>>> news:uJ%236BUH2GHA.4264@TK2MSFTNGP05.phx.gbl... >>>>> >>>>>>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 >>>>>>_______ >>>>>> >>>>>> >>>>>>"Roger B." <rb10@canada.com> wrote in message >>>>>>news:%23v7csED2GHA.3656@TK2MSFTNGP04.phx.gbl... >>>>>> >>>>>>>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 >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> -- >>>> >>>> Andy Pope, Microsoft MVP - Excel >>>> http://www.andypope.info >>> >>> >> >> > > |
|
|
|
17-09-2006, 10:20 PM
|
#9 |
|
Guest
Posts: n/a
|
Re: Formating textbox on a chart
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 _______ "Roger B." <rb10@canada.com> wrote in message news:eBvYn0o2GHA.3812@TK2MSFTNGP06.phx.gbl... > 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 > > > > "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message > news:O2d6xEh2GHA.3656@TK2MSFTNGP04.phx.gbl... >> 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 >> _______ >> >> >> "Roger B." <rb10@canada.com> wrote in message >> news:uSbHslT2GHA.3428@TK2MSFTNGP05.phx.gbl... >>> 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 >>> >>> >>> "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message >>> news:epQcXcR2GHA.3464@TK2MSFTNGP03.phx.gbl... >>>> 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 >>>> >>>> >>>> "Andy Pope" <andy@andypope.info> wrote in message >>>> news:eBV8I1J2GHA.1040@TK2MSFTNGP06.phx.gbl... >>>>> 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 >>>>> >>>>> Roger B. wrote: >>>>>> 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 >>>>>> >>>>>> >>>>>> "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message >>>>>> news:uJ%236BUH2GHA.4264@TK2MSFTNGP05.phx.gbl... >>>>>> >>>>>>>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 >>>>>>>_______ >>>>>>> >>>>>>> >>>>>>>"Roger B." <rb10@canada.com> wrote in message >>>>>>>news:%23v7csED2GHA.3656@TK2MSFTNGP04.phx.gbl... >>>>>>> >>>>>>>>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 >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>>> -- >>>>> >>>>> Andy Pope, Microsoft MVP - Excel >>>>> http://www.andypope.info >>>> >>>> >>> >>> >> >> > > |
|
|
|
18-09-2006, 12:07 AM
|
#10 |
|
Guest
Posts: n/a
|
Re: Formating textbox on a chart
Will do,
Roger "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message news:OiLYQ8p2GHA.4756@TK2MSFTNGP04.phx.gbl... > 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 > _______ > > > "Roger B." <rb10@canada.com> wrote in message > news:eBvYn0o2GHA.3812@TK2MSFTNGP06.phx.gbl... >> 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 >> >> >> >> "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message >> news:O2d6xEh2GHA.3656@TK2MSFTNGP04.phx.gbl... >>> 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 >>> _______ >>> >>> >>> "Roger B." <rb10@canada.com> wrote in message >>> news:uSbHslT2GHA.3428@TK2MSFTNGP05.phx.gbl... >>>> 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 >>>> >>>> >>>> "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message >>>> news:epQcXcR2GHA.3464@TK2MSFTNGP03.phx.gbl... >>>>> 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 >>>>> >>>>> >>>>> "Andy Pope" <andy@andypope.info> wrote in message >>>>> news:eBV8I1J2GHA.1040@TK2MSFTNGP06.phx.gbl... >>>>>> 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 >>>>>> >>>>>> Roger B. wrote: >>>>>>> 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 >>>>>>> >>>>>>> >>>>>>> "Jon Peltier" <jonxlmvpNO@SPAMpeltiertech.com> wrote in message >>>>>>> news:uJ%236BUH2GHA.4264@TK2MSFTNGP05.phx.gbl... >>>>>>> >>>>>>>>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 >>>>>>>>_______ >>>>>>>> >>>>>>>> >>>>>>>>"Roger B." <rb10@canada.com> wrote in message >>>>>>>>news:%23v7csED2GHA.3656@TK2MSFTNGP04.phx.gbl... >>>>>>>> >>>>>>>>>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 >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>>> -- >>>>>> >>>>>> Andy Pope, Microsoft MVP - Excel >>>>>> http://www.andypope.info >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > |
|
|
|
|
| Thread Tools | |
| Rate This Thread | |
|
|
