How to Create Index Sheet Hyper-linked to Other Sheets

Some days ago, I was working with some data which was distributed in about 18 sheets within a single Excel file. I was supposed to make calculations and then present that file to senior management. Now presentation was important as well. I must had made it easier for others to go through data. First thing i did was obviously name all the sheets so that they speak for the nature of data contained within. Next thing was to insert another sheet at start and create links from that sheet to all other sheets in the file.

To solve above problem, I created an ‘Index’ sheet. But its not easy to add hyper-links to 18 other sheets manually. It would have taken a lot of time and energy as well.

I wrote a small code snippet to create index file using VBA.

Simply open VBA editor (ALT+F11) and paste below function there. Run the macro ‘createIndex’ from Macro list. It will create a new sheet named ‘Index’ at start of the document. Names of all the sheets are copied to newly created sheet and those names are linked to the sheet as well. Please make sure that when you run this macro, there is no sheet named Index already.

Sub createIndex()
Dim i As Integer
Sheets.Add before:=Sheets(1)
Sheets(1).Name = "Index"
counter = Sheets.Count
i = 2
Do
Range("A" & i).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'" & Sheets(i).Name & "'!A1", TextToDisplay:=Sheets(i).Name
i = i + 1
Loop Until i = counter + 1
End Sub

Keyboard shortcut for Autosum option in Excel

Working with Excel in office environment extensively needs you to work with keyboard. You need to be quick to be productive. Time and motion study proves it that making use of keyboard shortcuts reduces time required to accomplish a task. You must learn the short cuts for the tasks you use more frequently.

Autosum option in Ribbon

Probability is quite higher that you use autosum option quite frequently. When applied, this deploys SUM function on selected cell such that cells above that are added. If there is no value above, preceding cells in the row are checked and sum of the values is returned.

hold ALT and press +

This task can be done quickly using keyboard shortcut. You only need to hold ALT and press + button. And SUM function as described above is implemented on selected cell. Like Cell A6 was selected and ALT+ is pressed to get sum of cells above selected cells.

Calculate Age and Remaining Days in an Event using DateDif in Excel

You might need to calculate your age in Excel. There may be requirement to find number of years, months and days between two dates. Excel function DateDif helps us find this difference: Syntax for this function is as under: DatedIf (first Date, … [Continue reading]

How To Add Colored Cells in Microsoft Excel

Use of SUM formula is very common to add a range of cells. You can also use SUMIF as well if some condition has to be fulfilled for addition of certain cells. But if we have to add certain cells from a range on basis of cell background color or font … [Continue reading]

How to Assign Keyboard Shortcut to Open Excel in Windows

If you are used to keyboard¬†short-cuts, it really makes life easier. You can really do tasks way faster. You have plenty of keyboard shortcuts once you are working in Excel. But you want to open using a shortcut? It's very easy. Simply go to the … [Continue reading]

Working With Excel

Era of electronic spreadsheets started from VisiCalc in 1983 and now well developed version of Microsoft Excel 2010 is available at this moment and too much is to appear yet. Name any profession where employees use computers, Excel exists there. No … [Continue reading]