星期三, 二月 22, 2012

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.


filtersheetrep05


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.


filtersheetrep01


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.


filtersheetrep03


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.


filtersheetrep02


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"


filtersheetrep04


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


filtersheetrep06


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)


filtersheetrep07





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


星期三, 二月 15, 2012

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 )


offset function


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


offset example


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.


Scenarios


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


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.


combo box


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.


scenario


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


profit and loss



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


conditional formatting


Step B: Then using the $ referencing intelligently.


$ referencing


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




星期一, 二月 06, 2012

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.


image


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


image


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


image


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.


fastfilter01


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


fastfilter02


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.


fastfilter03


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.


fastfilter04


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.


Sum02b


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.


countifs01


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:

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.


AdvancedFilterList01


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.


AdvancedFilterList02



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.


AdvancedFilterList03


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


___________________


星期四, 一月 12, 2012

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.


pirates


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.


pivotmultichange01


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.


pivotmultichange02


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