Create An Index Of Your Excel Worksheets

If your Excel workbook contains numerous worksheets, you can end up spending a good deal of time searching through them to find the one you are looking for. Why not optimize your time and create an index sheet that has links to all the worksheets. This way when you are looking for a specific worksheet, you can reference the index sheet instead of clicking through all the worksheets.

You can create an index by following the procedure described below:

1. Open your Excel workbook.
2. Insert a new worksheet at the beginning of the workbook. To do this, right click the first worksheet and click Insert. Select worksheet and click OK.
3. Right click the new worksheet and click Rename. Type in Index and press Enter.
4. Click the Tools menu, point to Macros, and click Visual Basic Editor.
5. Click Sheet1 (Index) and from the View menu click Code.
6. Paste the following code in:

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim M As Long
M = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = “INDEX”
.Cells(1, 1).Name = “Index”
End With

For Each wSheet In Worksheets

M = M + 1
With wSheet
.Range(“H1”).Name = “Start” & wSheet.Index
.Hyperlinks.Add Anchor:=.Range(“H1″), Address:=””, SubAddress:=”Index”, TextToDisplay:=”Back to Index”
End With
Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1), Address:=””, SubAddress:=”Start” & wSheet.Index, TextToDisplay:=wSheet.Name

Next wSheet

End Sub

7. Click the Excel icon on the toolbar.
8. Save the Excel workbook and close it.

When you reopen the workbook, the index should be listed with links to all the worksheets. Each worksheet will also contain a link that will return you to the Index worksheet.

Courtesy: Diana Huggings

Run web query at regular intervals

Was trying to download spot exchange rates on to excel and see its intra-day movement. The exchange rate markets are pretty volatile and it’s tough to track it by copy + paste every now and then. So this does the job.
1. Click on one of the cells in the worksheet and go to Data -> Import External Data -> New Web Query. Learnt about this feature recently and found it damn useful every time data needs to be copied from the webpage (works only on those which don’t require user-authentication).
2. Downloaded the data but I got this “EUR/USD : 1.3367 – 1.3370” as a string in a single cell(say B2) rather than individual items. So to extract individual BID (sell) and ASK (buy) price, used these formulas
– Right (Left (B2, 16), 6) to give 1.3367 as bid price
– and Right (B6, 6) 1.3370 as ask price respectively. [RIGHT returns the last character or characters in a text string, based on the number of characters you specify; LEFT returns the first character or characters in a text string, based on the number of characters you specify. Check excel help for more info]
3. Set the whole thing under macro and wanted this to refresh data every 2 minutes (people who are in Forex trading might think otherwise that it would prove costly to check every 2 min, they wouldn’t blink for a second not to miss the quote) and insert new set of data for every tick. All you would need is set the timer for the macro so that it runs at every 2 minutes.
4. And here is the output I needed.