MrExcel Excel Videos

Enter a product on Sheet 1. You want a VLOOKUP formula to grab the description from a lookup table on Sheet 2. However, if the product is missing from Sheet 2, you want Excel to stop, ask for the correct description, and then add that to the Lookup table so future entries for this product will get the proper description. Today's episode uses a VBA Macro to achieve this result. The video is based on the question originally posted here: How to Create a dynamic Unique list that updates automatically You can copy the VBA code from that link so you do not have to type the code.

Every once in a while, you run into ugly data sets and this qualifies. Someone decided it would be brilliant to put the text category and the sales amount in a single cell such as "WXYZ123". Today's duel is how to get the total sales for each category. Mike and Bill go back and forth 4 times today, offering a solution with Text to Columns, a Pivot Table, Array Formulas, and a VBA UDF.

Mike asks how he can hide 100 lines of text from Word in a single Excel cell. When the cell is selected, the text will appear. This episode 1866 shows one way to do this using cell comments. There are likely other ways - please post how you would solve this in the YouTube comments.

Dale needs to upload listings to eBay. One of the tough fields is the Gallery field, where the names of several images need to be in a single cell separated by semi-colons (Bill1.jpg;Bill2.jpg;Bill3.jpg). Given two cells that contain the image prefix and the number of images, can an Excel formula generate this gallery list? It feels like there should be an array formula to do this, but I had to resort to a VBA user defined function to solve the problem.

Given a start date and an end date, how do you produce a list of all the day numbers? For example, between Feb 28 and March 5, you would want the list 29,1,2,3,4,5. Mike and Bill show several different ways to solve this question.

James T asks if the Ctrl+G Dialog Box provides a way to jump 3000 columns to the right. (After all, Column E + 3000 columns is what? AAA? AAB? How would you know?) R1C1 style referencing is one ugly option, but Bill has something easier.

Chuck from Florida asks how to require a password to open an Excel file. Using Protect Workbook does NOT solve this. Learn where Microsoft has hidden this setting.

Latasha asks how to VLOOKUP to three worksheets. Her product number might be found on the master product list, but it might be on the old product list, or it might be on the rare product list. She needs a formula that will get the country code from one of those three sheets. Today's episode shows one way to solve this problem. There might be others - post your alternative in the YouTube Comments.

Rich wants to know how to allocate a contract amount of N months, where N is a value shown in column A. Rich is looking for a formula, no VBA. Today's Duel shows a few solutions.