Kristina asks: Hi Oscar,Your formula works great, however, I was wondering if there is capability to add another countif criteria so that it produces a random unique number different from the one above AS WELL AS the one to the […]

## Archive for the ‘Automate’ Category

Kristina asks: Hi Oscar,Your formula works great, however, I was wondering if there is capability to add another countif criteria so that it produces a random unique number different from the one above AS WELL AS the one to the […]

I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for each month and a summary sheet. The above picture shows you January 2017, simply enter the project name in column A […]

Tesh asks: How easy is it to modify this for recurring tasks (weekdays, weekly, monthly, quarterly and yearly) and maybe show a monthly view? Times are less important than just showing what is due on what day. I made a […]

Table of contents Basic schedule Round-robin tournament Double round-robin tournament Macro Download workbook How to use custom function According to wikipedia a round-robin tournament is a competition where all plays all. Excel is a great platform for building a round-robin […]

The user defined function demonstrated below, resizes a range you specify to columns or rows you also specify. The first argument is the range, second argument is how many columns you want and third argument is how many rows you […]

This vba macro lets you search for zip files in a folder. Then unzip those files to a folder you specify. It continues with sub folders until all zip files have been unzipped. What happens when you execute the macro? Select […]

Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there are 8 rounds. Look what happens with the scoreboard as I type new values in round 3.

This workbook lets you split expenses evenly with other people. Type name, expense and amount in the excel table on sheet 'Expenses'.

Search all workbooks in a folder is a popular post, I am happy so many find it useful. rusl cato asks: hi thanks for the great macro really makes a hard job much easier, can this be made to search […]

You can group rows or columns manually using the "Group" feature. That will tie a range of cells together so they can be collapsed or expanded. First you need to prepare your data, remove blanks, sort the data and insert […]

I found a really interesting workbook that calculates the shortest distance from a start cell to an end cell. It is all made with excel formulas, not a single vba line! It uses a grid of 12 by 12 cells […]

Today I will show you how to build a basic animated line chart. The chart has a small trailing shadow effect. How I made this chart The chart data is in cell range B3:J8. A small vba macro calculates the difference […]

This chart animates one bar at a time. The "Clear chart" button clears all values. The "Animate" button starts the animation. Check out my other animated chart:An animated excel chart If you think this animation is too slow, see the […]

Today I will share a To-do list excel template with you. You can add text to the sheet and excel creates checkboxes instantly. Click a checkbox and the related text is marked as finished. Also, if you delete text the corresponding […]

Today I´ll show you how to rank teams using excel formulas. The following example ranks soccer teams in the Premier League. But first, how are soccer teams ranked in Premier League? Teams are ranked by total points, then goal difference, […]

Click button "Show / Hide worksheets" to show or hide worksheets entered in cell range B6:B7. Instructions Here is how I did it. Create a button Go to Developer tab on the ribbon Click "Insert" button Click "Button" button Drag on […]

Click a single cell in column D to hide or show the comments in column E. Selecting multiple cells won´t change anything. VBA Code Where to put the code? Right click on sheet name Click "View Code" Paste code to […]

Today let's learn how to create a simple pivot table calendar! The animated gif below shows you the pivot table and two slicers. Instructions I am going to store my calendar data on another sheet named data. Step 1 - […]

Today I´ll show you how to create a useful hyperlink in excel. If you click the link it will take you to the first empty row in a data set. Formula in cell C2: =HYPERLINK("[Quickly jump to last row using […]

Rudy asks in this post Use a mouse hovering technique to create an interactive chart: Is it possible to create this interactive chart into interactive candlestick chart to compare two or more charts ? Hover over a company name and […]

Rahul asks: i want to know that when we create a vlookup sheet, and in the name column we enter a name and excel shows all details and a photo. Answer: Formula in cell C4: =INDEX(Data!B2:B9,MATCH(C2,Data!A2:A9,0)) Formula in cell C6: […]

Table of Contents Repeat values Repeat the range according to criteria in loop BatTodor asks: I failed to find right article in your blog and therefore I want to ask you in newest post. So I have table similar like […]

The picture below shows a table with data validation applied. If a user tries to enter an overlapping date range, the following error message appears. How to create the data validation If you apply data validation to a table, all […]

Today I´ll show you how to search a table column and jump to that table cell using the hyperlink function. When you click the cell that contains the HYPERLINK function, Excel goes to that location in the table. Formula in cell […]

Here is how to copy filter criteria from an excel table and use the same table filters on another table. Table1 is on sheet1 and table2 is on sheet2. Both tables must have the same header names and in the […]

My previous post Excel calendar (vba) used vba and formulas to extract events. This post demonstrates how to filter a table using a calendar. Select a date on the calendar and events on that specific day are automatically filtered. I would certainly have […]

Jinesh asks: I have to combine 200 columns into one list. I know. I tried steps from 'Combine cell ranges into a single range while eliminating blanks' UDF, but looks like typing the formula itself is going to be a […]

The following macro let´s you select a cell range and then the macro creates sheets with the same names as the selected cells. Now you can easily create many sheets very quickly. VBA macro Explaining code Creating procedures in excel […]

I found a basic interactive chart on the chitika website and my first thought was if I could do this in excel. So now I would like to share this simple chart with three buttons. You can select multiple buttons […]

Let´s say you do a lot of searches in two tables. The tables are related so it would be great if the second table is simultaneously filtered depending on the filtered values from the first table. Example, You want to […]

Cell B3 contains a drop down list (Data Validation). The values in drop down list are from column H. I am using a dynamic named range. Enter a value in cell E3 and press "Add" button to add the value […]

This tutorial shows you how to list excel files in a specified folder and create adjacent checkboxes, using vba. The last macro opens selected files. List files in a folder The following macro is assigned to the "Refresh list" button. […]

The macro provided in this blog post let´s you copy table design settings to other tables in the same workbook. Table style options and table style are copied and applied to other tables. You begin selecting the source table. Run […]

Rodney Schmidt asks: I am a convience store owner that is looking to make a spreadsheet formula. I want this formula to use information from one spreadsheet to auto-populate another spreadsheet on the next tab. I want the date the […]

This post demonstrates using a single button (form control) to turn a macro on or off. Assigned vba macro I am not going to post the add and remove checkboxes vba code again. You can find them here: Excel vba: […]

Danielle asks: I have a schedule that I am working with and based on one date (ie. 6/4/12) different processes take different times to complete (ie. one step could only take a week, another could take up to 4 weeks). […]

In some cases it can be useful and timesaving to automate data entering. The vba examples here all enter a value or formula in a cell if a value is entered by the user in an adjacent column. Example 1 - […]

Table of Contents Applying data validation lists dynamically using a table Applying data validation lists dynamically (vba) Applying data validation lists dynamically using a table Create a table Select cell range A1:C11 Go to tab "Insert" Click "Table" button Select […]

Fatou asks: Going back to my question, I had created a table and used the data to create a chart. However, something happened with the chart and now it is not updating when new data is added to the table. […]

This blog post demonstrates how to quickly change chart data range. I have created a drop down list (form control) above the chart and selected input range:$E$2:$E$4. Cell range $E$2:$E$4 contains the table names from sheet 2011, 2010 and 2009. See […]

Chirag asks: I want to separate numbers from following text: Abc123bx45 as a result 123 and 45 should be in different cells. This is a monster size array formula in cell C5 and it was created in excel 2007. The […]

Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps you plan and track various elements of a project. A dynamic chart automatically adds new values to the chart. Let´s start! […]

In this post I am going to demonstrate how to quickly apply a filter to a table. I am using the selection change event to apply the filter. Click on a cell in a table and the cell value is instantly […]

This post describes a basic user defined function that searches multiple ranges and adds corresponding values. You can also use multiple search values. VBA code: Function SumifAMS(lookup_value As Range, ParamArray […]

In a previous post: How to create a dynamic pivot table and refresh automatically in excel I demonstrated how to refresh a pivot table when a sheet is activated. This post describes how to refresh a pivot table when data is […]

This post demonstrates how to automatically add new values to a drop down list and a chart. This tutorial contains three steps: Create two named ranges Create a drop down list Set up a chart Create two named ranges Click […]

Question: Answer: This array formula creates a dynamic range, filtering the 10 last data. Adjust cell ranges $A$1:$A$25 in formula below. Array formula in cell F18: =AVERAGE(INDEX(A:A, LARGE(IF($A$1:$A$25<>"", ROW($A$1:$A$25), ""), 10)):INDEX(A:A, MATCH(9,9E+307, $A$1:$A$25))) How to create an array formula Copy (Ctrl […]

In this post I am going to explain the dynamic named range formula in Sam's comment. The formula adds new rows and columns instantly to the named range. This makes the named range dynamic meaning you don´t need to adjust cell references every […]

Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where you enter your bills due date and their frequency. However what makes mine different than yours is I'd love for […]

This is follow up post to: Tracking a stock portfolio in excel (auto update) In this post we are going to calculate cost basis and returns. The calculations are simplified, commissions, stock splits and dividends are removed from calculations. In the […]

Question: I found a question here about tracking a stock portfolio. He would like to automatically create an overview table with a unique stock symbol per row. He also wants the range extended down to include new rows as they […]

Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price list with new values. Sheet 1 is the old price list. It contains 5000 products and amounts. Sheet2 is the […]

Question: How do I filter common values between two ranges using array formula in excel? Answer: Formula in B12: =INDEX(One, MIN(IF((COUNTIF(Two, One)>0)+COUNTIF(B12:$B$12, One)=1, ROW(One)-MIN(ROW(One))+1, "")), MATCH(0, NOT(COUNTIF(Two, INDEX(One, MIN(IF((COUNTIF(Two, One)>0)+COUNTIF(B12:$B$12, One)=1, ROW(One)+1, ""))-MIN(ROW(One)), , 1)))+COUNTIF(B12:$B$12, INDEX(One, MIN(IF((COUNTIF(Two, One)>0)+COUNTIF(B12:$B$12, One)=1, ROW(One)+1, […]

Question: How do I filter values existing in one range but not in an other? Answer: Formula in B13: =INDEX(One, MIN(IF((COUNTIF(Two, One)=0)+COUNTIF(C12:$C$12, One)=1, ROW(One)-MIN(ROW(One))+1, "")), MATCH(0, COUNTIF(Two, INDEX(One, MIN(IF((COUNTIF(Two, One)=0)+COUNTIF(C12:$C$12, One)=1, ROW(One)-MIN(ROW(One))+1, "")), , 1))+COUNTIF(C12:$C$12, INDEX(One, MIN(IF((COUNTIF(Two, One)=0)+COUNTIF(C12:$C$12, One)=1, ROW(One)-MIN(ROW(One))+1, […]

Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and the original values, as seen on the picture below. To create a unique list from two columns or two cell […]

Question: How can I compare two columns to find common values? Array formula in C2: =INDEX($A$2:$A$11, SMALL(IF(COUNTIF($B$2:$B$11, $A$2:$A$11), MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)), ""), ROWS($A$1:A1))) How to create an array formula Select cell C2 Click in formula bar Copy Paste array formula to formula […]

Question: How do I create a chart that dynamically adds the values, as i type them in the workbook? Answer: Excel 2007 (and later versions) users, create an excel defined table. Excel 2003 (and earlier versions) users, create a dynamic […]