Posts by btadams

Here's a formula you can use. In this example, enter the numbers 1 thru 20 in cells A1:A20 on Sheet2 and in cells B1:B20 enter the formula =Rand()

Then use this formula:
=INDEX(Sheet2!A1:A20,RANK(Sheet2!B1,Sheet2!B1:B20))

Here's a macro that makes a hyperlinked index sheet (any chart sheets will be replaced with regular sheets with the chart as an object):

Sub MakeIndexSheet()
Dim i As Integer
Dim Sheetname As String

intResponse = MsgBox("This macro will create an index page of the worksheets in the active workbook." & vbCrLf & "Any worksheet named 'Index Sheet' will be replaced." & vbCrLf & vbCrLf & "NOTE: any chart worksheets will be converted to regular worksheets" & vbCrLf & "with the chart inserted as an object", vbInformation + vbOKCancel, "Create Index Page")
If intResponse = vbOK Then
On Error Resume Next
Application.ScreenUpdating = False
Sheets("Index Sheet").Select
Select Case Err.Number
Case 9
Sheets.Add Sheets(1)
Sheets(1).Select
ActiveSheet.Name = "Index Sheet"
On Error GoTo 0
Case 0
Application.DisplayAlerts = False
Sheets("Index Sheet").Delete
Application.DisplayAlerts = True
Sheets.Add Sheets(1)
Sheets(1).Select
ActiveSheet.Name = "Index Sheet"
On Error GoTo 0
Case Else
GoTo errorhandler
End Select

On Error GoTo errorhandler
Sheetcount = ActiveWorkbook.Sheets.count
Range("D2").Select
ActiveCell.Value = "Contents"
ActiveCell.Font.Size = 16
ActiveCell.Offset(0, 1).FormulaR1C1 = _
"To return to the index page, right-mouse click on the worksheet scroll buttons 34"
With ActiveCell.Offset(0, 1).Characters(Start:=80, Length:=2).Font
.Name = "Marlett"
.FontStyle = "Regular"
.Size = 10
End With

ActiveCell.Offset(2, 0).Select
For i = 1 To Sheetcount
If Sheets(i).Type = -4167 Then
temp = "'" & Sheets(i).Name & "'"
If Sheets(i).Name <> "Index Sheet" Then
ActiveCell.Value = Left(temp, Len(temp) - 1)
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=temp & "!A1"
'
' ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
' temp & "!A1", TextToDisplay:=Left(temp, Len(temp) - 1)
ActiveCell.Font.Size = 12
ActiveCell.Offset(1, 0).Select
End If
Else 'Selected sheet is a Chart sheet
Sheets(i).Select
On Error Resume Next
Sheets.Add
On Error GoTo 0
Sheets(i).Select
NewSheetname = ActiveSheet.Name
OldSheetname = Sheets(i + 1).Name
Sheets(i + 1).Select
ActiveChart.ChartArea.Select
ActiveChart.Location Where:=xlLocationAsObject, Name:=NewSheetname
ActiveSheet.Shapes(1).ScaleWidth 1.48, msoFalse, msoScaleFromBottomRight
ActiveSheet.Shapes(1).ScaleHeight 1.48, msoFalse, msoScaleFromBottomRight
ActiveSheet.Shapes(1).ScaleWidth 1.29, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes(1).ScaleHeight 1.28, msoFalse, msoScaleFromTopLeft
DoEvents
ActiveSheet.ChartObjects(1).Select
With Selection.Font
.Size = 10
.Bold = True
End With
ActiveSheet.Name = OldSheetname
Windows(ActiveWorkbook.Name).Activate
Range("a1").Select
ActiveWindow.DisplayGridlines = False
With ActiveSheet.PageSetup
.Orientation = xlLandscape
End With
Sheets("Index Sheet").Select
temp = "'" & Sheets(i).Name & "'"
ActiveCell.Value = Left(temp, Len(temp) - 1)
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=temp & "!A1"
ActiveCell.Font.Size = 12
ActiveCell.Offset(1, 0).Select
End If
Next i
Columns("D:D").EntireColumn.AutoFit
Range("A1").Select
ActiveWindow.DisplayGridlines = False
On Error Resume Next
ActiveSheet.SetBackgroundPicture Filename:= _
"C:\Program Files\Common Files\Microsoft Shared\Stationery\Ivy.gif"
On Error GoTo 0
ActiveWindow.DisplayHeadings = False
Range("D2").Select

End If
errorhandler:
If Err <> 0 Then
MsgBox Err.Number & ": " & Err.Description
On Error GoTo 0
End If
Application.ScreenUpdating = True
End Sub