Posted on February 17th, 2009 by Joseph Harris
The #N/A error happens most often when a lookup function cannot find a result. You can manually remove the all of your #N/As or you can stop them in your original formula. A simple and effective way of trapping the #N/A errors and replacing them with whatever you would like is to use the ISNA() [...]
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 [...]
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 [...]
Posted on February 9th, 2009 by Joseph Harris
Excel Solver is back in Excel 2008…
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 [...]
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 [...]
