Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 15th 04, 01:55 PM
Rita Palazzi
 
Posts: n/a
Default Bold part of formula results

Windows XP Professional
Office 2000

I have a formula that produces a sentence based on the value of a cell:

=IF(H18=0,CONCATENATE("Shpt Volume Increased ",TEXT(H18,".0%"),"
Y-O-Y"),CONCATENATE("Shpt Volume Decreased ",(TEXT(-H18,".0%"))," Y-O-Y"))


I want the word "Increased" or the word "Decreased" to be bold in the
resultant cell. Does anyone know the correct way to achieve this?

Thanks in advance for any help you may provide!

Rita Palazzi
Senior Engineer / Global Trade Services
FecEx Express


  #2   Report Post  
Old December 15th 04, 01:58 PM
Frank Kabel
 
Posts: n/a
Default

Hi
this is not possible with formulas

--
Regards
Frank Kabel
Frankfurt, Germany

Rita Palazzi wrote:
Windows XP Professional
Office 2000

I have a formula that produces a sentence based on the value of a
cell:
=IF(H18=0,CONCATENATE("Shpt Volume Increased ",TEXT(H18,".0%"),"
Y-O-Y"),CONCATENATE("Shpt Volume Decreased ",(TEXT(-H18,".0%")),"
Y-O-Y"))

I want the word "Increased" or the word "Decreased" to be bold in the
resultant cell. Does anyone know the correct way to achieve this?

Thanks in advance for any help you may provide!

Rita Palazzi
Senior Engineer / Global Trade Services
FecEx Express



  #3   Report Post  
Old December 15th 04, 02:21 PM
Gary Rowe
 
Posts: n/a
Default

Try putting "Shpt Volume Increased" and "Shpt Volume Decreased" in separate
cells, say A1, A2, bold the Increased and Decreased words and then refer to
them in your formula: =IF(H18=0,CONCATENATE($A$1,TEXT(H18,".0%"),"
Y-O-Y"),CONCATENATE($A$2,(TEXT(-H18,".0%"))," Y-O-Y"))



"Rita Palazzi" wrote:

Windows XP Professional
Office 2000

I have a formula that produces a sentence based on the value of a cell:

=IF(H18=0,CONCATENATE("Shpt Volume Increased ",TEXT(H18,".0%"),"
Y-O-Y"),CONCATENATE("Shpt Volume Decreased ",(TEXT(-H18,".0%"))," Y-O-Y"))


I want the word "Increased" or the word "Decreased" to be bold in the
resultant cell. Does anyone know the correct way to achieve this?

Thanks in advance for any help you may provide!

Rita Palazzi
Senior Engineer / Global Trade Services
FecEx Express


  #4   Report Post  
Old December 15th 04, 07:02 PM
Debra Dalgleish
 
Posts: n/a
Default

You can't format part of a formula result.

Perhaps you could use conditional formatting instead. For example, if
the formula is in cell D5:

Select cell D5
Choose FormatConditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type: =ISNUMBER(SEARCH("increase",D5))
Click the Format button
On the Font tab, select a font colour and style, e.g. Green, Bold
Click OK, click Add
For the second condition, use the formula:
=ISNUMBER(SEARCH("decrease",D5))
and use a different font colour.


Rita Palazzi wrote:
Windows XP Professional
Office 2000

I have a formula that produces a sentence based on the value of a cell:

=IF(H18=0,CONCATENATE("Shpt Volume Increased ",TEXT(H18,".0%"),"
Y-O-Y"),CONCATENATE("Shpt Volume Decreased ",(TEXT(-H18,".0%"))," Y-O-Y"))


I want the word "Increased" or the word "Decreased" to be bold in the
resultant cell. Does anyone know the correct way to achieve this?

Thanks in advance for any help you may provide!

Rita Palazzi
Senior Engineer / Global Trade Services
FecEx Express



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #5   Report Post  
Old December 16th 04, 07:51 PM
Ron Rosenfeld
 
Posts: n/a
Default

On Wed, 15 Dec 2004 07:55:41 -0600, Rita Palazzi wrote:

Windows XP Professional
Office 2000

I have a formula that produces a sentence based on the value of a cell:

=IF(H18=0,CONCATENATE("Shpt Volume Increased ",TEXT(H18,".0%"),"
Y-O-Y"),CONCATENATE("Shpt Volume Decreased ",(TEXT(-H18,".0%"))," Y-O-Y"))


I want the word "Increased" or the word "Decreased" to be bold in the
resultant cell. Does anyone know the correct way to achieve this?

Thanks in advance for any help you may provide!

Rita Palazzi
Senior Engineer / Global Trade Services
FecEx Express


You need to use VBA to accomplish this.

Depending on your setup, you would probable execute this formula in a VB
worksheet change module. The output from the VB macro would be a string which
would go into the cell. When there is just a string in a cell, and not a
formula, a portion of that string can be bolded.

Here is an example that might get you started. To enter it, right-click on the
worksheet tab, then paste the code below into the window that opens. If you
enter something in H18, you will see the result, with the one word bolded, in
H1.

========================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, Chg As Range
Dim res As String 'Result will be stored here

Set c = [H1] 'This is where the result will be
Set Chg = [H18] 'Cell to be tested

If Intersect(Target, Chg) Is Nothing Then Exit Sub

res = "Shpt Volume "
Select Case Chg
Case Is 0
res = res & "Increased "
Case Is < 0
res = res & "Decreased "
Case Else
res = res & "Unchanged "
End Select

res = res & Application.WorksheetFunction.Text(Chg, ".0%;.0%;;") & " Y-O-Y"

With c
.Value = res
.Characters(13, 9).Font.Bold = True
End With
===========================

--ron


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting an Excel formula to an Access query formula Adam Excel Discussion (Misc queries) 1 December 15th 04 03:38 AM
I want the results of a formula to show in cell, NOT THE FORMULA! ocbecky Excel Discussion (Misc queries) 4 December 10th 04 08:39 PM
What instead of an array formula part 2 Reg Besseling Excel Discussion (Misc queries) 2 December 10th 04 07:35 AM
Paste is is copying in formula, but display is wrong. Matt Excel Discussion (Misc queries) 2 December 7th 04 08:37 PM
Excel2K: Is it possible to use dynamic named ranges in custom data validation formula? Arvi Laanemets Excel Discussion (Misc queries) 0 December 2nd 04 11:29 AM


All times are GMT +1. The time now is 05:42 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017