For those who prefer a text explanation:

Format the cell “border” to add a diagonal from the left-top to the right-bottom (it’s one of the line choices in the Format Cells dialog box | Borders tab.

Then, type several spaces the literal Hours ALT+ENTER to create a newline in the text and then the literal Days.

Adjust the number of spaces before the literal Hours to get the desired effect.

An alternative, which is simple albeit crude, is to type Days space \ space Hours. {grin}

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/data_visualization/06%20Visual%20Effects.shtml

Tushar Mehta

The solution, as many know, is to enter a formula in the destination worksheet that references the source cell, e.g., =’C:\Temp\[Book2.xlsx]Sheet1′!$E$5

Given the high likelihood of making an error in entering long formulas, I decided to see if I could automate the process.

Tip 1: In doing so, I discovered that under certain circumstances Excel will make a very interesting correction. If the source workbook has a single worksheet, then one can use any sheet name in the formula and Excel will change it to the correct one! So, if book2.xlsx contains a single sheet named Sheet1, and one were to enter the incorrect formula =’C:\Temp\[Book2.xlsx]abc’!$E$5, Excel will correct it to =’C:\Temp\[Book2.xlsx]Sheet1′!$E$5.

That aside, since the cells in the source worksheet are not selectable, one cannot construct the formula using click-and-point. So, I decided that as long as one wants the values from the source cells to be in the same cell in the destination worksheet, why not select the cells in the destination worksheet? The code below does just that. Also, there is no longer a need to open the shared server workbook at all!

One final note. I rarely use so many different interactions with the consumer, preferring a userform. But, the below is easier to share.

Tip 2: The Inputbox method gets a single piece of information from the user, e.g., the sheet name in the code below. If the user were to cancel the resulting dialog box, the method returns False. The usual way to check for this is to compare the returned value with “False”. But, this precludes a legitimate response of “False”! So, I tend to check if the returned type is a boolean. The same applies to the GetOpenFilename method.

Enter the code below is a standard VBE module. Then, open the destination worksheet (or create a new one), and then run the linkToExternal subroutine. It will ask for the source workbook, the source worksheet, and then the destination cells. The code will add in each destination cell a formula that links to the same cell in the source worksheet.

Option Explicit

Sub linkToExternal()
    If ActiveWorkbook Is Nothing Then
        MsgBox "Please open the destination workbook before running this macro"
        Exit Sub
        End If
    Dim FName
    FName = Application.GetOpenFilename( _
        Title:="Please select the source workbook")
    If TypeName(FName) = "Boolean" Then Exit Sub
    FName = Left(FName, InStrRev(FName, Application.PathSeparator)) _
        & "[" & Mid(FName, InStrRev(FName, Application.PathSeparator) + 1) _
        & "]"
    Dim SheetName
    SheetName = Application.InputBox("Please enter the name of the source sheet", Type:=2)
    If TypeName(SheetName) = "Boolean" Then Exit Sub
    FName = "='" & FName & SheetName & "'!"
    Dim Rng As Range
    On Error Resume Next
    Set Rng = Application.InputBox( _
        "Please select the destination cells into which you want the corresponding source cell values", _
        Type:=8)
    On Error GoTo 0
    If Rng Is Nothing Then Exit Sub
    Dim aCell As Range
    For Each aCell In Rng
        aCell.Formula = FName & aCell.Address(True, True)
        Next aCell
    End Sub

The emphasis is on the use of conditional formatting to accomplish the task. The minimal VBA code required to make it work is the same single executable statement for all of the different highlighting options!

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/0121%20highlight%20row%20and%20col%20of%20selected%20cell.shtml

Tushar Mehta

1) dynamically add controls to a userform
2) respond to events for these controls, and
3) specifically respond to events using a callback procedure that is located in another class module!

Since this may come across as a fairly technical topic, this tip utilizes the above capabilities to provide a functional solution:

1) list in an userform the names of all available fonts with each name shown using that font,
2) hover over the option button associated with a font to see a sample of every English keyboard character in that font,
3) click on the option button to select the font, and, finally,
4) use this capability to programmatically get the user’s selection, if any.

Below is an example of the font selector in action. Each OptionButton shows the name of one available font using the font itself. At the same time, the control tool tip shows the font name in English (see the Wide Latin tip). A sample of how every keyboard character will look in that font appears below the font selector frame.

The motivation for this example was a Daily Dose of Excel blog post by Michael (http://www.dailydoseofexcel.com/archives/2012/03/14/getting-a-font-list-to-a-combo-box-2/). He used a combo box to list the fonts available to Excel leveraging a technique shown in a tip by John Walkenbach (http://www.j-walk.com/ss/excel/tips/tip79.htm).

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1054%20show%20fonts%20in%20userform.shtml

Tushar Mehta

So, with TM Retro Slicer, one can create a slicer in any version of Excel from 2003 to 2010.

TM Retro Slicer works with versions of Excel from 2003 to 2010. Once enabled, a slicer will be visible on the worksheet.

Switching worksheets will hide the slicer and it will reappear when the worksheet is reactivated.

The slicer will remain across a workbook close and re-open.

For more on this shareware solution, please visit http://www.tushar-mehta.com/excel/software/retro_slicer/index.htm

– Tushar Mehta

For more please visit http://www.tushar-mehta.com/excel/charts/normal_distribution/

– Tushar Mehta

There are a number of posts, easily found through Google or Bing, that address the same issue, including my own Find a set of amounts that match a target value. This add-in packages the analysis in an easy-to-use add-in, including a means to stop the analysis if it is taking too long and preserve the combinations found so far, and various options on what results should be shown.

For more please visit http://www.tushar-mehta.com/excel/software/match_target/.

– Tushar Mehta

Microsoft BI ‏ @MicrosoftBI
Congrats to @tusharm10 who is the final winner of the #MSPV contest today! Thank you everybody for playing; new set of questions coming Tue!

I wonder when (if?) PowerView will find its way to Excel.

For all the tweets regarding the contest search Twitter for #MSPV.

And, yes, to enter the contest I had to create a Twitter account. You can find me at @tusharm10

PowerView contest details: http://blogs.msdn.com/b/microsoft_business_intelligence1/archive/2012/02/16/the-power-view-contest-is-here.aspx

– Tushar Mehta

Or, one might want to compare 6 products A, B, C, D, E, and F on 6 different attributes: Aesthetic Appeal, Compatibility, Strength, Market Size, Durability, and Reliability. The Custom Radar Chart lets one easily compare all the products along each of the dimensions on interest. This is the example used in the documentation. With the axes normalized, the resulting chart would be

For more, please visit http://www.tushar-mehta.com/excel/software/custom_radar/index.htm