Filter Excel Data Onto Multiple Sheets

From Contexture.com

Filter Excel Data Onto Multiple Sheets:

There is a sample Excel file on the Contextures website that has a list of orders, and sales rep names. You can click a button, and a sheet is created for each sales rep, with that person's orders.

There is another version of the file, and it creates a sheet for each sales rep name that is visible, after an AutoFilter has been applied.

How It Works

When you click the worksheet button, a macro runs. It sets up a list of sales rep names, then loops through that list, creating a worksheet for each name.

To get the orders onto each sheet, the macro uses an Advanced Filter, setting up a criteria range in cells L1:L2 on the worksheet.

In the screen shot below, the first name from the list, Gill, has been entered into the criteria range.

Advanced Filter Criteria Problems

The filter works well, unless there is another name that begins with the same characters as one of the full names. In the screen shot below, the orders for Giller have been included on the Gill orders sheet.

A Giller sheet was also created, and it has the correct list of orders for Giller.

Why did this happen? The Advanced Filter assumes that we are looking for anything that BEGINS with "Gill", so it includes Giller. It's as if there is an invisible "*" wildcard character at the end of the Sales Rep name.

To get an exact match only, we can change the criteria cell, so it uses an equal sign before the name. To do this manually, we would enter this formula in cell L2:

="=" & "Gill"

Fix the Problem in the VBA Code

In the original Excel VBA code, the sales rep's name is entered into cell L2.

.Range("L2").Value = c.Value

I revised the code, so it includes the equal sign and quote marks, just as the manually-entered formula would have.

.Range("L2").Value = "=""="" & " & Chr(34) & c.Value & Chr(34)

Now, when you run the macro, only the exact matches for a sales rep name will appear on the list (upper and lower case will not affect the results).

Download the Sample File

To test the Advance Filter macros, you can download the sample files from the Contextures website.

On the Sample Excel Files page, go to the Filters section. Then, look for these files:

• FL0013 - Create New Sheets from Filtered List


• FL0004 - Create New Sheets from List

Advanced Filter Video

To see the steps for a manual Advanced Filter to a different worksheet, you can watch this short Excel video tutorial.

Or watch on YouTube: Automatically Copy Excel Data to Other Sheet

______________

RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog .

You can read the full article here: Filter Excel Data Onto Multiple Sheets

Please visit the Contextures page on Facebook

Reporting Scenarios using Offset

From chandoo.org

Reporting Scenarios using Offset:

Project Managers often report financial numbers to the management. In a dynamic world, these numbers are usually based on a lot of factors that may or may not be under your control. So the top management demands that the numbers be reported as per different economic scenarios – Optimistic, Normal or Pessimistic.

It is important to report and present the numbers in a usable format to the top management. They should be able to toggle the scenarios comfortably and see the results. Offset function comes to your rescue to ensure a great looking model with the flexibility of reporting multiple scenarios.

What is the offset function?

A few months back, I had written about the offset function and how it can be used to create flexible models. I had discussed at that point of time, why offset function is one of the most versatile functions and at the same point of time quite dangerous as well.

In this tutorial, we would see another usage (I feel simpler than last time!) of the offset function

If I were to borrow the signature of the function from my last post, the offset function reads something like: Offset( range, rows, columns, height, width )

I will use a similar example, but change the usage of the function a little bit!

So in the illustrated example, it starts from the C3 cell, moves 1 rows and 3 columns and then gives the value (15 in this case)!

This time Offset is NOT returning an array. It is returning a single value!

[Related: OFFSET, VLOOKUP & MATCH explained in simple words]

So how can this be useful?

The offset function can move the reference of the cell by n rows and m columns. That means that if I structure the sheet with different economic possibilities in different rows, I can always move the scenarios using offset function.

How was this achieved?

Step 1: The layout of the sheet helps me achieve this objective very easily. The first part of the sheet to select the scenarios is achieved using form controls

Step 2: The Scenarios are listed in sequential order, one after the other and the form control (combo box in this case) is linked to the name of the scenarios.

Step 3: Depending on the scenario selected, the index number of the selection changes. This number is fed into the growth rate and cost selection using the offset function.

Step 4: The model is linked to the selected scenario to report the P&L figures

As I told you, offset function is quite versatile in nature and can help you achieve a lot of flexibility in your model

Bonus Step

From your PM career you would have known that preparing a nice looking report as important (if not more) as generating correct results! In our scenario selection model, we highlight the selected scenario (the pink colored row) to give clarity to the end user. This is achieved using

Step A: A simple formula in conditional formatting and

Step B: Then using the $ referencing intelligently.

What functions do you use in reporting?

I am sure that if you are generating flexible reports and dashboards for reporting, you would be using some interesting functions and tools in Excel. I use Offset, Index, Match, Indirect, Mod. Which ones do you use?

Templates to download

I have created a template for you, where the subheadings are given and you have to link the model! You can download the same from here. You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).

Also you can download this filled template and check, if the information you recorded, matches mine or not!

Next Steps

Chandoo and I are running a course on Excel for Project Managers to share with you the various tools and techniques in Excel that can make you an awesome Project Manager. We comprehensively cover aspects related to Planning, Tracking and Reporting apart from Basics of Finance and Advanced Techniques like Monte Carlo Simulation in Project Management in the course. If you are interested in learning more about the course, you can click here.

For any queries regarding the using Excel for Project Management, feel free to put the comments in the blog or write an email to paramdeep@edupristine.com

Excel AutoFilter By Typing Criteria

from contextures.com

Excel AutoFilter By Typing Criteria:

Someone emailed me for help with an Excel AutoFilter last week. He wanted to type the criteria onto a worksheet, and have the filtered results shown automatically. There are some built-in options for filtering by text, and keep reading to see a worksheet version that Roger Govier designed.

AutoFilter Search in Excel 2010

There is a new feature in Excel 2010 that provides easy searching, though not on the worksheet. You can see an example here, for the Excel 2010 AutoFilter search feature.

AutoFilter Search in Earlier Versions

In earlier versions of Excel, you can filter for text, but it's a bit more work. In Excel 2007 you can use a text filter, which opens the Custom AutoFilter dialog box

In Excel 2003, use the Custom option on the AutoFilter drop down.

Roger Govier's FastFilter

If you'd like to enter the AutoFilter criteria on the worksheet, instead of a search box or dialog box, you can use Roger Govier's FastFilter sample Excel file.

He has set up a table on the worksheet, with an empty row above the table. In that row, you can type one or more criteria, and when you press the Enter key, the table is automatically filtered.

For a simple filter, type an exact match for a value, and press Enter. In the screen shot below, the table is showing only the items from category 2.

You can also use operators, and in the next screen shot I've added a ">20" criterion in the Unit Price column.

Use WildCard Characters

If you're trying to find a specific string of characters in a column, you can use the * and ? wildcard characters. In the next screen shot, I used *b* in the product name column, to find any products that have a "b" somewhere in the name.

Use Multiple Criteria in a Column

You can use special characters for OR (^^) and AND (^), to combine multiple criteria in a single heading cell. In the Category ID column, I used the ^^ characters to find category 2 OR 4. In the Unit Price column, the ^ character limits the price to >20 AND <35.

Remove the Criteria

To clear the filter from a column, just click on the criteria cell, and press the Delete key on your keyboard. If you want to clear all the filters, select all the criteria cells, and press Delete.

Download the Sample File

To download the sample file, you can visit Roger's Sample Files page on the Contextures website. In the Filters section, look for FL0001 - Fast Filter. There is a download link for the FastFilter zipped file.

The file is in Excel 2003 format, and will work in later versions too. After you open the file, enable macros, so you can test the automatic filter feature.

____________

RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog .

You can read the full article here: Excel AutoFilter By Typing Criteria

Please visit the Contextures page on Facebook

Excel SUMIFS Sum With Multiple Criteria

from contextures.com

Excel SUMIFS Sum With Multiple Criteria:

In Excel 2007 and Excel 2010, you can use the new SUMIFS function to sum items using multiple criteria.

For example, sum the orders where an account status is Active, and the number of visits is greater than or equal to 10. You can type in the criteria, but it's better to use cell references, where possible.

I've updated the Excel SUM page on the Contextures website, to include the SUMIFS function example and video.

Watch the SUMIFS Video

To see the steps for creating a SUMIFS formula, and using cell reference, you can watch this short Excel video tutorial.

Or watch on YouTube: Excel SUMIFS Sum With Multiple Criteria

_____________

RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog .

You can read the full article here: Excel SUMIFS Sum With Multiple Criteria

Please visit the Contextures page on Facebook

Use Excel COUNTIFS to Count With Multiple Criteria

from contextures.com

Use Excel COUNTIFS to Count With Multiple Criteria:

In Excel 2007 and Excel 2010, you can use the new COUNTIFS function to count, based on multiple criteria. For example, in a list of orders, you can find out how many orders were for pens, and had a quantity of 10 or more.

I have updated the Contextures COUNT Functions page, to include a COUNTIFS example, and video demo. If you are using Excel 2003, or earlier versions, you can use the SUMPRODUCT function instead. There is an example for that function on the COUNT page too.

Watch the COUNTIFS Video

To see the steps for using the COUNTIFS function, you can watch this short Excel video tutorial.

_________

RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog .

You can read the full article here: Use Excel COUNTIFS to Count With Multiple Criteria

Please visit the Contextures page on Facebook

Remove Flags from Inbox Emails

From codeforexcelandoutlook.com

Remove Flags from Inbox Emails:

In Don’t let senders flag message with Reminder for me there is a method for removing follow up flags from emails. Here I will present a VBA method (both manual and event-driven) for doing the same.



Personally, I dislike those follow up flags. The mere fact that an email was sent implies that some kind of response or follow up is being requested. A flag is just piling on. I will decide if and when to follow up on an email.

Manual / On Demand Method

This code may be run as needed to remove flags on existing emails in the default Inbox.

Sub RemoveFlags()
 Dim itms As Outlook.Items
 Dim msg As Outlook.mailItem
 Dim i As Long

 ' get default Inbox folder items
 Set itms = GetItems(GetNS(GetOutlookApp), olFolderInbox)

 For i = 1 To itms.count
   If TypeName(itms.Item(i)) = "MailItem" Then

     Set msg = itms.Item(i)
     ' remove flags
     With msg
       .FlagStatus = olNoFlag
       .FlagIcon = olNoFlagIcon
       .Save
     End With

   End If
 Next i
End Sub

This code should be placed in a standard module in Outlook's VBA editor (see Where do I put my Outlook VBA code for placement assistance).

Event Handler

Suppose you wanted to remove flags on all incoming emails automatically. Kudos to you for being brave. I started with the event handler found at Stock Event Code and produced this code:

Private WithEvents Items As Outlook.Items

Private Sub Application_Startup()
 Set Items = GetItems(GetNS(GetOutlookApp), olFolderInbox)
End Sub

Private Sub Items_ItemAdd(ByVal item As Object)
 On Error GoTo ErrorHandler

 Dim msg As Outlook.mailItem

 If TypeName(item) = "MailItem" Then
   Set msg = item
   With msg
     .FlagStatus = olNoFlag
     .FlagIcon = olNoFlagIcon
     .Save
   End With
 End If
ProgramExit:
 Exit Sub
ErrorHandler:
 MsgBox Err.Number & " - " & Err.Description
 Resume ProgramExit
End Sub

This code should be placed in the ThisOutlookSession module in Outlook's VBA editor (see Where do I put my Outlook VBA code for placement assistance). Restart Outlook and then ask someone to send you an email with a follow up flag.

Ancillary Functions

Note that both of the above sections of code need these procedures. Paste them into a standard module.

Function GetItems(olNS As Outlook.NameSpace, _
                    folder As OlDefaultFolders) As Outlook.Items
 Set GetItems = olNS.GetDefaultFolder(folder).Items
End Function
Function GetNS(ByRef app As Outlook.Application) As Outlook.NameSpace
 Set GetNS = app.GetNamespace("MAPI")
End Function
Function GetOutlookApp() As Outlook.Application
 Set GetOutlookApp = Outlook.Application
End Function

Related Articles:

• January 9, 2012 -- Annoying Website Name Generator in VBA and PHP
• December 26, 2011 -- Validating Userform Data
• July 29, 2011 -- New Outlook Blog

Remove Flags from Inbox Emails is Copyright © JP Software Technologies. All Rights Reserved.

Excel Filter for List Items: Shark Week 2011

From Contentures.com

Excel Filter for List Items: Shark Week 2011:

On Monday, we declared this Excel Advanced Filter Week, in honour of the three filter feeding shark species. Who said Excel wasn’t exciting?

Today, we’ll see how to use the Advanced Filter to select only the items that are in a list, when running the filter.

Advanced Filter Criteria Range

For this Advanced Filter, we want to filter for orders that total more than $1000, and have that criterion in the criteria range, shown below.

We also want to limit the products – only including the items that are listed in column I.

Filter for List Items in Advanced Filter

For a short list of items, you could create multiple rows in the criteria range, and list each item separately. For longer lists, that would be impractical.

Instead, you can use a formula, to check each row, and see if its product is in the list. We’ll add a second column to the criteria range, and put the formula there.

In this example, the first product data is in cell C2, so the formula in cell G2 is:

=COUNTIF(I:I,C2)

The COUNTIF function returns the number of instances of the product in the list in column I. If the product isn’t found, the count is zero. Only the records with a count greater than zero would pass through the filter.

Because you’re using a formula in an Advanced Filter criteria range, the heading can’t match any of the source data headings. You can either clear the heading cell in the criteria range, or type a different heading. I left cell G1 blank, and the criteria range is F1:G2.

Run the Advanced Filter

After you set up the criteria range, you can run the Advanced Filter. Remember, if you want the results on a different worksheet, select that destination sheet before you run the filter.

In this example, the filter is started from the ListOrders sheet, and the list and criteria range are on the Orders sheet.

Download the Advanced Filter List Workbook

To see the sample data, and test the filter, you can download the Advanced Filter for List Items sample workbook. The file is in Excel 2007 format, and is zipped.

Watch the Advanced Filter for List Items Video

To see the steps for setting up the criteria range, and running the filter, you can watch this short Excel Video tutorial.

Or watch on YouTube: Excel Advanced Filter Items in a List

___________________

Get a Table from a Web Page with an XML Request

from www.dailydoesofexcel.com

Get a Table from a Web Page with an XML Request:

I’m tired of trying to remember the library names and the syntax for doing this. I’m putting here so when I search for XMLRequest, Web Request, HTTPRequest, I can find it.

Sub GetData()



Dim oHttp As MSXML2.XMLHTTP

Dim sHtml As String

Dim hDoc As HTMLDocument

Dim hTable As HTMLTable

Dim hRow As HTMLTableRow

Dim hCell As HTMLTableCell

Dim rStart As Range



Const sURL As String = "http://www.contextures.com/xlsampledata01.html"



Set oHttp = New MSXML2.XMLHTTP

Set hDoc = New HTMLDocument

Set rStart = Sheet1.Range("A1")



'Send the web request

oHttp.Open "GET", sURL

oHttp.send



'Give it enough time to process

Do

DoEvents

Loop Until oHttp.readyState = 4



'put the web page into an HTML Document

hDoc.body.innerHTML = oHttp.responseText



'Find the right table and write it to a sheet

For Each hTable In hDoc.all.tags("TABLE")

If hTable.Rows(0).Cells(0).innerText = "OrderDate" Then

For Each hRow In hTable.Rows

For Each hCell In hRow.Cells

rStart.Offset(hRow.RowIndex, hCell.cellIndex).Value = hCell.innerText

Next hCell

Next hRow

End If

Next hTable



End Sub

It sends a request to the sample data page over at Contextures, jams that into an HTML Doc, then writes the table out to a sheet.

Change All Pivot Tables With One Selection

from contextures.com

Change All Pivot Tables With One Selection:

Happy New Year! I hope you had a safe and happy New Year's Eve celebration, and are off to a good start in 2012. Things got a bit rowdy at the Contextures office party, and I found these guys passed out on the floor, the next morning.

Change All Pivot Table Filters

Despite the wild parties, I was able to get some work done over the holidays. There is a new sample file on the Contextures website, that changes all the pivot tables, when you change a report filter in one pivot table.

For example, if you change the "Item" report filter in one pivot table, all the other pivot tables with an "Item" filter will change. They get the same report filter settings that were in the pivot table that you changed.

Select Multiple Items

In this version of the sample file, the "Select Multiple Items" setting is also changed, to match the setting that is in the pivot table that you changed.

In the screen shot below, the Item field has the "Select Multiple Items" setting turned off. If any other pivot tables in the workbook have an "Items" filter, the "Select Multiple Items" setting for those fields will also change.

How It Works

The multiple pivot table filtering works with event programming. There is Worksheet_PivotTableUpdate code on each worksheet, and it runs when any pivot table on that worksheet is changed or refreshed.

For each report filter field, the code checks for the Select Multiple Items setting, and changes it on all the pivot tables with the same report filter field. The code loops through all the worksheets in the file, and through each pivot table on each sheet.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Dim wsMain As Worksheet
Dim ws As Worksheet
Dim ptMain As PivotTable
Dim pt As PivotTable
Dim pfMain As PivotField
Dim pf As PivotField
Dim pi As PivotItem
Dim bMI As Boolean

On Error Resume Next
Set wsMain = ActiveSheet
Set ptMain = Target

Application.EnableEvents = False
Application.ScreenUpdating = False

For Each pfMain In ptMain.PageFields
   bMI = pfMain.EnableMultiplePageItems
   For Each ws In ThisWorkbook.Worksheets
       For Each pt In ws.PivotTables
           If ws.Name & "_" & pt <> wsMain.Name & "_" & ptMain Then
               pt.ManualUpdate = True
               Set pf = pt.PivotFields(pfMain.Name)
                       bMI = pfMain.EnableMultiplePageItems
                       With pf
                           .ClearAllFilters
                           Select Case bMI
                               Case False
                                   .CurrentPage = pfMain.CurrentPage.Value
                               Case True
                                   .CurrentPage = "(All)"
                                   For Each pi In pfMain.PivotItems
                                       .PivotItems(pi.Name).Visible = pi.Visible
                                   Next pi
                                   .EnableMultiplePageItems = bMI
                           End Select
                       End With
                       bMI = False

               Set pf = Nothing
               pt.ManualUpdate = False
           End If
       Next pt
   Next ws
Next pfMain

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Download the Sample File

To test the code, you can download the sample file from the Contextures website. On the Sample Excel Files page, in the Pivot Tables section, look for PT0025 - Change All Page Fields with Multiple Selection Settings. The file will work in Excel 2007 or Excel 2010, if you enable macros.

Watch the Excel Video Tutorial

To see the steps for copying the code into your worksheet, and an explanation of how the code works, you can watch this short Excel video tutorial.

Or watch on YouTube: Change All Pivot Tables When One Changes

______________________

RSS Footer: Thank you for subscribing to the RSS feed for Contextures Blog .

You can read the full article here: Change All Pivot Tables With One Selection

Please visit the Contextures page on Facebook