Month: March 2005

Angled Column Heading

Posted on by Rob van Gelder

Every time it comes to Column Headings I struggle to get meaningful text into such a small box.

There are a few approaches I’ve used:

  • Widen this Column just for sake of the heading
  • Double the Row Height, widen the Column a bit then Word Wrap
  • Widen the Column just enough to let Shrink To Fit do the rest
  • Abbreviate the heading, put the long version in a cell comment

    • A little while ago I starting rotating the Column Heading text as a way to save space…

  • 90° Text Orientation – get the column as narrow as possible

    • I’ve settled on a less extreme Text Orientation, like 30°.

    But what is really great about Text Orientation is the way Vertical Borders react – they go angled too!

    I think the borders really help to guide a user from the column heading the column values.

    It would be interesting to know what you do when it comes to formatting headings… Click Comments and let us know!

    Two Type Charting

    Posted on by Rob van Gelder

    Excel provides some nice Charting features. One really nice feature is the ability to combine chart types.
    Here’s how to graph KPI data together with both a line and column chart.

    You will need the Charting toolbar for some of it. Right-click the toolbar and select Chart.

    I’ll start off with some monthly data for the last two years.

    E14 and F15 are =AVERAGE(B2:B13) and =AVERAGE(C2:C13) respectively.

    Highlight A1:F15 and use the Chart Wizard to create a simple line chart without markers.
    Because Avg 03 and Avg 04 have only one value, they cant be seen in a line chart.
    From the Charting toolbar, use the dropdown box to select Series “Avg 03?. Then from the Chart menu, select Chart Type. Set it to a Column chart.
    The same needs to be done for Avg 04.

    I’ve set 2003 to Red, 2004 to Dark Teal, and KPI to Black. All three with a weight of 2.
    The Scale I set to Min:500, Max:1000, Maj:50, Min:50, Axis Crosses:500.
    Doubleclick the Avg 03 bar to set formatting options.
    Pattern:Red, Data label:Value=Ticked, Options: Overlap=100, Gap=5
    For Avg 04… Pattern:Dark Teal, Data label:Value=Ticked

    Doubleclick the number appearing over each bar to set formatting options.
    Font:White, Alignment:Label Position=Inside End
    I then did some general tidy up with the Plot Area.
    I made it bigger and set the Background to Light Green, Gridlines White

    Calculating Two ATP Functions in the Formula Bar

    Posted on by Dick Kusleika

    Dean found this bug and I found Dean in the .programming newsgroup. From what I can tell, if you have two or more functions from the Analysis Toolpak, you get a #NAME? error when you try to calculate in the formula bar.

    To calculate in the formula bar, you can use F9 or Cntl+= while a cell’s in edit mode. Here’s how it works, when it works right:

    formula in formula bar
    select part of a formula
    F9 part of a formula

    When the calced portion (or if the whole formula is calced) contains two ATP functions, you get this:

    select part of a formula with two ATP functions
    f9 two atp function give name error

    I’ve tested this only in Excel 2000. Maybe someone can test it in some other versions and post a comment. Also, I have a hard time believing this is only in the ATP. I tried it on a UDF that’s in a regular old xla file and did not have a problem. Honestly, I don’t know what kind of add-in the ATP is, but I’ll bet dollars to donuts it’s not written in VBA. Perhaps this is true of all COM add-ins or all xlls. If someone has those handy to test, let me know what you find.

    INDIRECT and Dynamic Ranges

    Posted on by Dick Kusleika

    This isn’t a new issue, but it’s new to me. Charlie, a loyal reader, was trying to use INDIRECT with a dynamic range name and kept getting errors.

    IndirectDynamic1

    =MAX(INDIRECT(“List2”))

    returns the #REF! error. It appears to be a limitation of INDIRECT (yes, another one).

    One way to get around the problem is to just reproduce the dynamic range name formula in the cell.

    =MAX(OFFSET(Sheet1!$B$6,0,0,COUNTA(Sheet1!$B:$B)-1))

    That works, but now when you change List2, you have to remember to change this formula too, not to mention all the other formulas that use this.

    Option #2 is a UDF.

    =MAX(DINDIRECT(“List2”))
    Public Function DINDIRECT(sName As String) As Range
        ‘It stands for Dynamic Indirect
        
        Dim nName As Name
       
        ‘Make sure the name supplied exists
        On Error Resume Next
            Set nName = ActiveWorkbook.Names(sName)
            Set nName = ActiveSheet.Names(sName)
        On Error GoTo 0
       
        ‘Set the function to the range or return the name error
        If Not nName Is Nothing Then
            Set DINDIRECT = nName.RefersToRange
        Else
            DINDIRECT = CVErr(xlErrName)
        End If
       
    End Function

    Gee, as simple as that function is you’d think Microsoft would have put in the program.

    Does anyone have an option #3?