MrExcel Excel Videos

Ams asks if there is some VBA that will tell you the previously selected cell. Say you are in A1, then click in B1. Is there any VBA that will tell you that the previous cell was A1? It seems like this should be possible. In today's episode, see how Bill puzzles out the solution by using the macro recorder to learn about Names then VBA AutoComplete to find the right property. Near the end of the video, I add two new names to show that the value of A1 was "X" when you selected it, but "Z" when you left it.

Tom sends in today's question. Enter age, gender, lab result and then lookup the rating in a table to indicate if someone's risk is low, moderate, high, or very high. Tom wants to return the result as a color (green, blue, yellow, red) via conditional formatting to the lab result cell. Today's podcast has a little of everything: rearranging the table, exact match, Less than Match, subtracting the number 1, OFFSET, assigning a formula to a defined name, then even a linked picture (aka Camera Tool) lookup for good measure. Bill always says that 99% of his VLOOKUPs end in False. For today's solution, that statistic drops to 25%.

You have a formula with 6 absolute references. You want to keep one of those constant, but change the other 5 references to point to a new range. Today's episode shows two methods - one involves dragging a range five times, the other is even more convoluted. However - the big discovery in this episode is that you can use F4 to change all nine references from Absolute to Relative, provided your selection does not begin or end on a cell reference.

Today's question: a formula is showing an answer in B2. Can you somehow repeat the input values in C2 so the person reading the spreadsheet can see the answer as well as what went into the answer. Bill and Mike duel it out with the & operator and the CONCATENATE function. Mike throws in a little ADDRESS function, and then Shift+F3 or Ctrl+A.

Jim sends in today's question. Jim is a fan of an iPad game called Dots. You have a 6x6 grid of dots that appear in four colors. You are trying to find the longest connection of dots of the same color. Jim wondered if Excel could generate a random 6x6 grid of four colors of dots to look for patterns. In today's episode, see how to use RANDBETWEEN, conditional formatting and a custom number format to generate the grid of dots. But finding patterns is going to be tougher. While the human brain can detect the patterns, a formula in Excel to find the longest connection would be tougher. The video ends with some conditional formatting to highlight the possible connecting cells but no method for finding the longest connection.

In the third episode about trying to hide blank rows, a simple pair of macros to show all and hide blanks. You won't lose the Undo feature for this workbook.

In episode 1736, the goal was to hide blank rows without using a macro. It turns out the real requirement is to hide blank rows without having to remember to run a macro. Thus, an Event Handler macro that would automatically run in the background is acceptable. In today's episode, learn how to set up an event handler macro that silently runs every time the worksheet is changed. This macro will hide rows that are non-numeric in column C.

Cathy asks if there is a way to hide rows without the assistance of a macro. After a quick failed attempt at using conditional formatting, Bill figures out a non-macro way to hide rows in this episode of the Learn Excel from MrExcel podcast.

Calculate the MEDIAN of the last 10 entries in a column. Bill uses OFFSET with COUNT. Mike uses an array formula from his upcoming Ctrl + Shift + Enter book to find the last 10 non-blank cells. To pre-order Mike's book: Amazon.com: Ctrl+Shift+Enter Mastering Excel Array Formulas: Do the Impossible with Excel Formulas Thanks to Array Formula Magic (0783324907186): Mike Excelisfun Girvin: Books