Replies

Or you could set up a small table elsewhere and use the LOOKUP function, or for more versatility, INDEX/MATCH combo.

Note, I updated my post after you posted. If possible you can change the headers in row 3, to be the first day of month, then use those as references in your formula.

Then change the formatting to Custom, mmmm, and they will appear as month names.

Howdy. You can add an IF wrapper around it (simplified) (Sorry, after looking at sheet, you have that)

I changed the SUMPRODUCT, and changed the headers in row 3. This is in G4

=IF($F4=””,””,SUMPRODUCT(($C$4:$C$148=$F4)*($D$4:$D$148>=G$3)*($D$4:$D$148<H$3)))

Seems to work

Office for Mac is always behind the Windows versions. One critical area is VBA for Office 2004, which is based on VB 5 the same as used for XL 97. And with 2008, there will not be any VBA at all. While I use Excel 2003 on Windows all day, I have Office 2004 on the Mac at home, I seldom use it. My primary work at home involves word processing and DTP – and for my purposes Mellel is far superior to Word 2004 for Mac. Word for Mac does not handle RTL correctly (but the Windows version has for years), whereas it is flawless with Mellel.

However, if you have the Intel Mac you can run Office 2003/2007 natively (same as Windows) on the Mac in Boot Camp. The results I have seen is that the Mac performance is at least equal to Windows and in some tests faster than Windows.

Howdy. Bill Jelen/Tracy Syrstad published a book, VBA and Macros for Microsoft Excel. One chapter is devoted to VBA and Pivot Tables. They strongly recommend using VBA for Pivot Tables, but creating the PT in code as an intermediate step only, eventually copying/pasting and formatting (all in code). I used their examples, then began applying that code to my own work. It really does work better than using Pivot tables. I encourage you to look at the book.

EDIT: Finally figured out how to format the code. This sets up a table with each color and the number on the palette.

Try this code:

Sub ColorTable()
' variables
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim sColorOrder As String
    Dim sLightColors As String
    Dim arColorOrder As Variant
    Dim iColorNr As Integer
    i = 0
    ' these are the colors in same order Excel shows
    ' them in the pulldown:
    sColorOrder = "1,53,52,51,49,11,55,56,9,46,12,10,14," & _
                  "5,47,16,3,45,43,50,42,41,13,48,7,44,6," & _
                  "4,8,33,54,15,38,40,36,35,34,37,39,2,17," & _
                  "18,19,20,21,22,23,24,25,26,27,28,29,30,31,32"
    arColorOrder = Split(sColorOrder, ",", , vbTextCompare)
    ' Light colors that will have a dark fontcolor:
    sLightColors = "|6|36|19|27|35|20|28|8|34|2|"
    Application.ScreenUpdating = False
    For j = 1 To 7            ' loop rows
        For k = 1 To 8            ' loop columns
            With Cells(j, k)
                iColorNr = arColorOrder(i)
                .Interior.ColorIndex = iColorNr
                .Value = iColorNr
                ' is the color light, then make the textcolor darker
                If InStr(1, sLightColors, "|" & iColorNr & "|") > 0 Then
                    .Font.ColorIndex = 56  'dark grey
                Else
                    .Font.ColorIndex = 2  'white
                End If
            End With
            i = i + 1
        Next k
    Next j
    ' Give it a nice layout:
    With Range(Cells(1, 1), Cells(7, 8))
        .RowHeight = 20
        .ColumnWidth = 4
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Font.Bold = True
    End With
    Application.ScreenUpdating = True
End Sub

Ah, no that persistent thing is a pain!

I put this one line code and attached to a toolbar button to get rig of it. Or you could assign a keyboard shortcut.


Sub HideReviewBar()
Application.CommandBars("Reviewing").Visible = False
End Sub


Appreciate the solution Hans.

I wonder, though, what happens with the MIN when one of the top 10 has a zero value. OP didn’t indicate one way or the other, but I know that on some projects I have worked on, there was a possibility that 0 occurred in the ranking, in which case the the MIN would have to be altered to be “MIN non-zero solution”. Just a thought.

Post deleted by shades

I agree, you can setup the connections between the db and Excel. Almost none of our people have Access. I have a four step (with four files) process, with Excel VBA to pull data through EssBase from our main frame. Then a second Xl file to accumulate ongoing data. Then a third step is to setup up display data in an XL file (we keep a running 13 week view, which we can choose the ending period for any view). And then each graph/table is linked (initially by VBA) into Powerpoint. Everything is done with formulas after the EssBase pull. With 99 markets, and 25 channels for each, plus three charts for each it is a rather substantial project. And the entire process takes only 45 minutes, 25 minutes of it involves the VBA pulls. I never touch the in-between worksheets, setup tables nor any graphs/charts.

I used this arrangement in a simpler process, and reduced report time from 10 hours, 15 minutes (and it could be faster with some code, but I have many other projects to do).

Howdy. In Tools > Options > Edit, the box next to “Fixed decimal” (on left side) should be unchecked. It might be checked and set to 2. This would be your system causing it. When I opened the spreadsheet it worked fine (no decimals).

I appreciate this hint on not activating the worksheet. It really is more efficient (and easier to understand).

Thanks

To avoid the “subtract 1” problem, I set up the data with an extra column between row headers and data, and columns headers and data. Then it is automatically adjusted. Then I also include one extra row at bottom and column to the right in the Data name definition. That way if I add data by inserting rows or columns inside the blank rows/columns, all formulas automatically adjust.

I use this layout of data for just about every major project. It has saved considerable time, and formulas never have to be adjusted/changed.

In regard to the second, you can use a work around.

Add a row above the data, below the headings. Then put 5 in cell D2. Also, put 5 in cell D7. Then extend the data source from row 2 to row 7.

Select your target line, change it to secondary axis, and change the scale to match the same scale as primary axis.

Then select the X-axis, choose “Format axis”, and in the scale tab, uncheck the box “Value (Y) axis crosses between categories”.

See Sheet2 for the final look.