Excel Links – Congratulations Team India Edition

Posted on April 4th, 2011 in excel links - 12 comments

Congratulations to Team India for winning Cricket World Cup 2011Wow, what a weekend it has been. Indian cricket team has won Cricket world cup 2011. It has been a highly entertaining tournament and the finals were just incredible.

Congratulations to Team India for winning Cricket World-cup 2011.

I am tempted to make a dashboard of sorts to show the journey of Team India in this world cup. But I could not get much time to work on anything. I will post something this week to celebrate the victory.

Meanwhile, join me and congratulate the team.

Recommended Excel Reading

It has been a while since we had an Excel Links post. So here we go.

Consolidating Multiple Sheets of Data in to One

Consolidation of data spread across multiple sheets is one of the most time consuming and frequently done process in Excel. In this article from tipsindeed, Gobish explains how to do this using only formulas. The formula based consolidation technique uses SUMPRODUCT, SUMIF, INDIRECT, ADDRESS formulas. Read more to learn about this. For more on Consolidation, refer to Consolidating data in Excel – How to?.

Getting Data to Excel from a website that requires log-in

Excel has a feature called as Web Queries [examples] to help us extract data from a website to your excel files. Now, this works very well if the website data is public. But what if the information you are trying to extract is behind a log-in screen? Web queries fail because they cannot login dynamically in to a site for you. Dick at DDoE shows us a macro based approach to dynamically submit the website login form to get thru to the data we want. Very interesting approach.

Show selected cells info in Excel Status Bar

Excel status bar is quite helpful. It gives you summary statistics of the data you are working with. It tells you which mode you are in. It also lets you quickly start recording a macro and so much more. But it doesn’t tell you which cells you have selected. So Mike at DataPig blog wrote a simple macro that shows the selected cells address range and dimensions in the status bar. This could be useful if you select data frequently and vigorously scratch your head going, ‘wait a minute, what did I select?’.

Interesting Chart on Increase in Housing Quality since 1940

This is an interesting chart on how the quality of housing (in US) has changed since 1940. It shows how the real value of a house has changed in the last 7 decades. What I liked most is that, it also shows a visual summary of various other things at the bottom along the timeline. Very interesting approach.

Interactive Dashboards in Excel – the next level

Robert at ClearlyandSimply shows us how much more we can get with Excel by combining features like conditional formatting, form & active-x controls and VBA. In fact, this is the same lesson he did for Excel School as a guest faculty. Very powerful ideas and quite elegant implementation too. Go ahead and explore the example on Robert‘s site.

Share a web site / resource with our readers

If you know a webpage or resource that can help us all learn Excel and become awesome, please drop the URL using comments. Also, you are welcome to suggest links for inclusion in Excel Links posts by emailing them to me at chandoo.d @ gmail.com.

You have a great week ahead.

PS: Cricket Worldcup victory image from Cricinfo.

Written by Chandoo
Tags: , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

12 Responses to “Excel Links – Congratulations Team India Edition”

  1. Carlos says:

    But... Is the housing quality chart done with Excel?

  2. Istiyak says:

    Hi chandoo! As u told to share something with our reader, i already sent one excel file to ur e-mail with the subject "NUMERIC TO WORD CONVERTER". But yet there is no response. This file convert numeric digit to word just with the help of formula. So, Plz check and revert.

  3. Pablo says:

    A really great Excel website that I recommend to everybody is excelunusual.com
    It covers many impressive Excel applications, and the explanations are very easy to follow.

  4. Paul Daniel says:

    Congrats India, though I thought it was nice of the Aussies let another team win the WC for a change 🙂

  5. Christian says:

    Yes, very polite of the Aussies.....

    Chandoo, I have noticed a change since first viewing the site around 3 yrs ago.

    Originally it seemed you had emphasis on Excel "without" VBA or macros.

    But now these feature more regularly.

    I don't have an opinion about whether they should or shouldn't be included, but just thought it was interesting to see the change in the site over time.

    Keep up your good work, and congrats to Team India!

    Christian

  6. Hui... says:

    @Istiyak
    Have you seen: http://support.microsoft.com/kb/213360

    @Carlos
    No it's not done in Excel, But there is no reason it couldn't be

  7. Carlos says:

    @Hui
    It would be very interesting to see how can we do it with Excel. It's hard to imagine (at least for me). Any brave Excel expert can help us?

  8. Chandoo says:

    I see that we have some strong Australian supporters 🙂

    @Pablo.. I am aware of excelunusual.com. I read it thru my google reader. I wish to share few of his articles thru next editions of excel links.

    @Christian: I still try to avoid VBA as much as possible. But I have realized its power overtime and I want to experiment more with it. Also, we will be opening VBA School shortly, so it makes sense to share what I am learning.

  9. Prasad says:

    Though I prefer to do things with Excel (Formulas)itself, bt some times VBA can be used as a Top Up fecility..Readers can make use of the following VBA code(preferably keep in your Personal file) to consolidate sheets in the given file.

    Sub ConsolidateSheets()
    Dim wssheet As Worksheet
    Dim lngLastRow As Long

    Sheets.Add
    Range("A1:Z1").Value = "Header"

    strConsolidate = ActiveSheet.Name
    ActiveSheet.Name = "Consolidate"
    For Each wssheet In ActiveWorkbook.Worksheets

    If wssheet.Name "Consolidate" Then
    wssheet.Select
    lngLastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Range("A2:Z" & lngLastRow).Select
    Selection.Copy
    ActiveWorkbook.Sheets("Consolidate").Select
    lngLastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    Range("A" & lngLastRow).Select
    ActiveSheet.Paste
    End If
    Next wssheet
    End Sub

  10. Istiyak says:

    @ hui : i am not searching for it but i had sent it with email for our users. u have to check it in mailbox of chandoo.

  11. Pablo says:

    Hi Chandoo,
    I'm glad you are going to offer the VBA school, definitively I will sign up for that one.
    At what level are going to take it, from beginner to expert?
    Cheers!

  12. "We should accept that Millennials are an important part of our company,but different from others.So we need to take into account their belliefs and lifestyle so they can give 100% at work.That's good for the company,right?"

Leave a Reply