Multi Rule Conditional Formatting

We had a website visitor looking to apply multiple conditional formats to a table. Two (2) of the rules were working, but the third just wouldn't apply correctly. We reviewed the rules and found a couple of quick files to resolve the problem.

Read More »

Calculated Conditional Formatting

I am trying to come up with a formula where if the sum comes to 0 in the next cell I want it to show Low, but if it comes to 1 then it should show Med or 2 or higher then it should show High....we chose to us the IF statement and combined it with conditional formatting to solve this request.

Read More »

To Do List for Multiple People

Using the Free To Do List Template change the update routine to allow for multiple worksheets to be created for different people.

Read More »

Convert a Letter to a Number

I am looking for a formula I can place in a column that will convert a letter to a number value. For instance, s=1, m=1.5, L=2.5. I want it to be a continous rule for the column, leaving find and replace not feasible. To solve this problem the built-in vLookup function can be used.

Read More »

Move filtered rows from a table to another sheet

I built a table in excel where I am comparing items. Some of those items are things will require further action after analysis. I created a column in the table with a drop down menu for yes/no. If the item requires further analysis I select "yes".

Read More »

Split Row Into Multiple Rows

For this request we needed to loop through a number of rows and where candidate data was separated by a comma split the text into however many separate values are found between the comma’s. As part of the split process to also divide an amount by the number of candidates to be split

Read More »

How to Force Users to Enter a Custom Format Using Data Validation

Microsoft Excel offers various approaches on providing specific formats for user input. We can use formula, data validation, format cells or VBA depending on the complexity of the format. For complex formats such as product ID and email address, we can use VBA and take advantage of the powerful regular expression. But since the requirement is for a formula in this case, using Data Validation feature is sufficient and as effective

Read More »

Count Total Number of Deposits by Month

I need a count if function to count cells which returns a date in the current month. We receive deposits on lots, column A is lot no. and Column B is date deposit paid. Refer to the attached sheet - I need formulas for the highlighted yellow cells. Thank you :) What we want basically here is to produce a report that includes the count of the total number of deposits for a particular month. However, the COUNTIF function will not accomplish the result we need. Instead, we will be using the powerful SUMPRODUCT function.

Read More »

VLOOKUP, HLOOKUP and LOOKUP Functions

If you are planning to step up your proficiency in Excel, you should learn how to use the look up functions. These functions are commonly used on tables that act like a database. While databases have SQL queries, which is challenging especially for non-programmers, Excel has these simple look up functions and the most widely used are VLOOKUP, HLOOKUP and LOOKUP.

Read More »

Filtering Dates in Multiple Columns

I am in need of a way to filter the information in the attached file. I need to be able to filter the entire sheet to show all row's that have a specified month/year no matter which column the specified month/year is in. This is used for my monthly report to show all after sales activity for each month. For example, at the end of the month I need to be able to filter all activities for the month of May 2014 to show all emails, phone calls, start-ups, acct notify, one year follow ups so I can convert that to PDF and send to the CEO

Read More »