Controlling EuroMillions:

In this example, we import data from the Internet (the last draw of EuroMillions). Imported data can be linked to another sheet.
Copying imported data (from column to row) to another sheet using an array formula.

Comparing data using an array formula.
Our profits can be seen with the help of an array formula.
Refresh Data.

Importing data from the Internet.

Open your web browser and surf to
http://www.euromillions.be/NL/Spelen_en_Winnen/Trekkingsspelen/Euromillions/default.aspx
Click on “Results”.                                                             Then click on “Latest results”.

 

                                 

Select and copy the address from the “Address bar”.
The Internet Explorer “Address bar”:

The “Address bar” of Firefox:

Close the Internet browser.
Open a new Excel file.
Click on the “Data” (1) tab in the “Ribbon” and click on the “From Web” (2) button.

In the “New Web Query”, right-click in the address (1), select “Paste” (2) and then click “Go” (3).

Excel will load the requested page in the “New Web Query”.

A word of explanation about the structure of websites. When you visit a random site on the Internet chances are that nine out of ten times, the site’s structure is built on tables, even though they may not always be clearly visible on the page.
To import data “From Web”, Excel will recognize these tables.
In the “New Web Query” these tables are indicated by a yellow arrow (1).

When we move the mouse over them, a text window will be displayed, with the text “Click to view the entire page” or “Click this table to select” and the color of the arrow will change to green and there will be a framework around the table (or entire page) to be signed, so we can see the contents of the table.
Once we have found the correct table for our purpose, click on the arrow and the table is selected for importing (2).

We now select the table containing the following information: the date of the draw, the numbers and stars, and the lottery amounts for the right combinations (1). Click on the arrow that stands for “National lottery official site” (2) and the table is selected for importing.
Click on “Import” (3)

Excel will propose to place the data in cell $ A $ 1, and we accept that and click OK.

The data is imported.

Right-click on the “Sheet1” (1) tab, select “Rename” (2) and rename it to “Results” (3).

Rename Sheet2 as “Check”.

Importing a data link to another sheet.

We are going to copy the dates that have a link, from the “Results” sheet to the “Control” sheet.
On the “Control” sheet, select cell C2 and, type the equals sign =. Click on the tab of the “Results” (1)sheet. We find the cell with the following data, “Draw of …………….”. This is normally cell B8. Click the cell B8 and press Enter.
We are now automatically redirected to the “Control” sheet and the text “Draw of …………….” is placed in cell C2.
Select the cells C2 to J2 and select “Merge and Center”. With the cells still selected, click on “Borders” (1), select an “Outside Edge” (2) and press Enter.

Select the cells C3 to G3 and click “Merge and Center”, click on “Borders”, select “Outside Borders”, type “Numbers” and press Enter.
Select the cells I3 and J3 and click “Merge and Center”, click on “Borders”, select “Outside Borders”, type “Stars” and press Enter.

Select the cells C6 to J6 , click “Merge and Center”, click on “Borders”, select “Outside Borders” and type “My combinations” or, if you’re in a group, you can type, for eg., “Our combinations” and press Enter.

Copyright © 2013 – Swotster Ltd.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats