If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Include formatting in a formula
Thanks, Dave and Mark.
Mark, since the entire formula is in one cell and I want to bold and underline certain parts of the result, I cannot bold and underline manually. "Mark" wrote: If you know which cell is going to be the results, couldn't you just Bold and Underline yourself? -----Original Message----- Formulas don't allow this kind of formatting. If you convert to a value, then you can do it, though. Ceci wrote: Hi, I appreciate if someone can help me with the formatting. Here is part of my formula: "YTD: As of " &TEXT(Datasheet!D3,"MMMM DD, YYYY") &" (" &TEXT(Datasheet!I4,"0%") The result is YTD: As of May 31, 2004 (42% of the year) Is there a way I can bold and underline the date? How can I include the formatting in the formula itself? Since the date is referencing a cell, I don't know how to format it in excel. Thanks for your help in advance. -- Dave Peterson . |
#2
|
|||
|
|||
Include formatting in a formula
You can do it with an event macro
Assuming your formula was entered in "Reportsheet" and that the date and % on Datasheet is static when another sheet has focus, put this in your Datasheet code module (right click on Datasheet's worksheet tab and choose View Code): Private Sub Worksheet_Activate() Dim dDate As Long Dim sDate As String Dim sPercent As String dDate = Sheets("Datasheet").Range("D3").Value - _ 1462 * ActiveWorkbook.Date1904 sDate = Format(dDate, "MMMM DD, YYYY") sPercent = Format(Sheets("DataSheet").Range("I4").Value, "0%") With Range("A1") .Value = "YTD: As of " & sDate & " (" & sPercent & ")" With .Characters(12, Len(sDate)).Font .Bold = True .Underline = True End With End With End Sub This will update whenever you switch into ReportSheet. If your Datasheet's values are not static, you could use a Worksheet_Change macro in Datasheet's code module instead. In article , "Ceci" wrote: Mark, since the entire formula is in one cell and I want to bold and underline certain parts of the result, I cannot bold and underline manually. "Mark" wrote: If you know which cell is going to be the results, couldn't you just Bold and Underline yourself? -----Original Message----- Formulas don't allow this kind of formatting. If you convert to a value, then you can do it, though. Ceci wrote: Hi, I appreciate if someone can help me with the formatting. Here is part of my formula: "YTD: As of " &TEXT(Datasheet!D3,"MMMM DD, YYYY") &" (" &TEXT(Datasheet!I4,"0%") The result is YTD: As of May 31, 2004 (42% of the year) Is there a way I can bold and underline the date? How can I include the formatting in the formula itself? Since the date is referencing a cell, I don't know how to format it in excel. |
#3
|
|||
|
|||
Include formatting in a formula
Thanks very much. I really appreciate. I will give it a try. Hope I don't have to bother you again. Thanks.
Ceci "JE McGimpsey" wrote: You can do it with an event macro Assuming your formula was entered in "Reportsheet" and that the date and % on Datasheet is static when another sheet has focus, put this in your Datasheet code module (right click on Datasheet's worksheet tab and choose View Code): Private Sub Worksheet_Activate() Dim dDate As Long Dim sDate As String Dim sPercent As String dDate = Sheets("Datasheet").Range("D3").Value - _ 1462 * ActiveWorkbook.Date1904 sDate = Format(dDate, "MMMM DD, YYYY") sPercent = Format(Sheets("DataSheet").Range("I4").Value, "0%") With Range("A1") .Value = "YTD: As of " & sDate & " (" & sPercent & ")" With .Characters(12, Len(sDate)).Font .Bold = True .Underline = True End With End With End Sub This will update whenever you switch into ReportSheet. If your Datasheet's values are not static, you could use a Worksheet_Change macro in Datasheet's code module instead. In article , "Ceci" wrote: Mark, since the entire formula is in one cell and I want to bold and underline certain parts of the result, I cannot bold and underline manually. "Mark" wrote: If you know which cell is going to be the results, couldn't you just Bold and Underline yourself? -----Original Message----- Formulas don't allow this kind of formatting. If you convert to a value, then you can do it, though. Ceci wrote: Hi, I appreciate if someone can help me with the formatting. Here is part of my formula: "YTD: As of " &TEXT(Datasheet!D3,"MMMM DD, YYYY") &" (" &TEXT(Datasheet!I4,"0%") The result is YTD: As of May 31, 2004 (42% of the year) Is there a way I can bold and underline the date? How can I include the formatting in the formula itself? Since the date is referencing a cell, I don't know how to format it in excel. |
Thread Tools | |
Display Modes | |
|
|