Month: November 2016

How To Create A Conditional Drop Down List

In this post we’re going to learn how to create a conditional drop down list in a cell. This means the drop down list will depend on some other value in the workbook and the available values in the drop down menu will change depending on this value. For example, we might want a user to be able to select from a film genre from a drop down list, then based on this we might want them to be able to select a film within that genre. To do this we will need to set up a few items...

Read More

How To Format A Telephone Number Using The TEXT Function

  Get The Completed Workbook   1 Formatting a 7 digit number   Let’s say we have a list of phone numbers and we want to have them formatted in the nice usual readable way. For example instead of seeing 2225678 we want to see 222-5678. We can use the following formula.   =TEXT(B3,"###-####")   2 Formatting a 7 digit number with an area code   What if the number has an area code and we want to format the phone number like this, (333) 555-6666? That’s pretty easy as well and we can use the same text formula...

Read More

How To Use Index And Match To Look Up Data With Multiple Criteria

Get The Completed Workbook Looking up a value based on one criteria   Let’s say we have a table with employees and their total sales and we want a formula to return the total sales based on the employee.     Now, if we want to get Allison’s sales, we could use a VLOOKUP formula using this formula.   =VLOOKUP(B7,A2:B5,2,FALSE)   Or, we could get fancy and use an index match combination using this formula.   =INDEX(B2:B5,MATCH(B7,A2:A5,0))   Looking up a value with a row criteria and a column criteria     Now suppose we have a similar table...

Read More

How To Generate A List Of File Names From A Folder Without VBA

Get The Completed Workbook In this post we’re going to explore how to go about getting a list of file names in a folder without using any VBA code. This method uses the little known / often forgotten Excel 4 XLM functions. These functions aren’t like Excel’s other functions such as SUM, VLOOKUP, INDEX etc. These functions won’t work in a regular sheet, they only work in named functions and macro sheets. For this trick we’re going to use one of these in a named function.     Create a named function. Select cell A1. Go to Formula tab in the...

Read More

How To Loop Through Various Objects In Excel VBA

In this post we’re going to explore how to loop through various objects in Excel using VBA. Using these snippets of code you’ll be able to loop through sheets, open workbooks, files in a folder and named ranges in a workbook. Loop through all sheets in an active workbook In this snippet of code we loop through all the sheets in the active workbook. We could use this “shell” code to do something like get all the sheet names in a workbook or perform some action on each sheet etc. Sub LoopThroughSheets() Dim ws As Worksheet For Each ws...

Read More

Follow Us

Advertisement

Free Book

Recent Tweets

Categories

Pin It on Pinterest