Archive for February, 2009

Find the Last Row with Data in Excel

Posted on February 16th, 2009 by Joseph Harris

Reader Writes: Joseph, I have a spreadsheet set up where I enter all of my companies “packages shipped” data. I would like to know the value of the last row that I inputted. So, if column A contains packages shipped on each row, I want to know the last number entered in column A. Thanks, Carl [...]

Automatic Updating “Dynamic” Charts in Excel

Posted on February 16th, 2009 by Joseph Harris

One of the most time consuming parts of producing a scheduled report is updating all of the charts in the report. Many times, an analyst only wants to show the last 7 days or the last 6 months of data and not the entire data set. To achieve this, the analyst changes the the Y [...]

Solver for Excel 2008 – MAC

Posted on February 9th, 2009 by Joseph Harris

Excel Solver is back in Excel 2008…

Vlookup Multiple Criteria in Excel

Posted on February 9th, 2009 by Joseph Harris

The standard vlookup function allows for one condition to be looked up. For example, if I have the data in rows 1 through 7 below, and I want to determine the favorite color of Dan, who was born in Jan and is 16 years old, the standard vlookup function will not work. The standard vlookup [...]

Conditional Formatting with Multiple Criteria in Excel

Posted on February 3rd, 2009 by Joseph Harris

Microsoft Excel has a limit of 3 criteria in Conditional Formatting. We can get around this with a bit of VBA code… Private Sub Worksheet_Change(ByVal Target As Range) Dim intclr As Integer If Not Intersect(Target, Range(“A1:z100″)) Is Nothing Then Select Case Target Case “ted” intclr = 6 Case 6 intclr = 12 Case 11 To [...]

ColorIndex Reference – Excel VBA

Posted on February 3rd, 2009 by Joseph Harris

A chart that can be used to look up the colorindex integer to see the corresponding index color [...]