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
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

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 color of those cells, Excel doesn’t has any built-in solution so far.

Only way to fulfill above requirement is to write a user defined function. We will write a simple function which will help us use a custom formula to add content of colored cells.

You will have to open VB Editor (short key is ALT+F11) and insert new module there. Simply paste below code inside new module.

Function SumIfColor(sumRange As Range, requiredColor As Range) As Variant
Dim dataCell As Range
For Each dataCell In sumRange
If dataCell.Interior.ColorIndex = requiredColor.Interior.ColorIndex Then
SumIfColor = dataCell + SumIfColor
End If
SumIfColor = SumIfColor
End Function

After you have pasted above code, as an easy example, we have below data where highlighted in green are to be added. We must have a cell where required color is fixed so as to compare cells from range.

Use of Custom Formula

We are going to add colored cells from E1 to E4. Our required color is in D5. To have our sum result in E5, simple write down above function name SumIfColor as formula like =SumIfColor(range of cells to be added , cell containing required color)

Formula in our example will be =SumIfColor(E1:E4,D5)

Please Note that whenever you change the color of any cell, you have to press CTRL+ALT+F9 to update formula result. Result of the formula doest get updates by F9 or even by reopening your Excel file.