Sorting - Is It Possible . . .

[BobH]

. . . in Excel to create in a cell, a formula that will execute a pre-defined sort on a worksheet?

Suppose I have a spreadsheet that contains multiple columns and rows. I want to publish the spreadsheet to others, but I want it protected so that no changes can be made to it. I would like for users to be able to sort the spreadsheet by either column A, B or C.

The spreadsheet is updated centrally by simply adding rows and populating cells in them. Rather than creating 3 spreadsheets sorted respectively by columns A, B and C and publishing them each time a row is added, I'd like to create 3 cells that contain formulas that will execute all of the necessary steps to sort appropriately. Users would be able to to simply select one of the cells and press enter and the spreadsheet would sort according the choice made.

Can this be done? How would one even start to create such a formula or macro?

Or is this something better suited to a different application other than Excel.

[HansV]

This requires some VBA code.

1) In the ThisWorkbook module:

Code: Select all

Private Sub Workbook_Open()
    Worksheets("Sheet1").Protect Password:="BobH", UserInterfaceOnly:=True
End Sub

This protects the sheet with password "BobH" but allows VBA code to be run on the protected sheet.

2) In a standard module:

Code: Select all

Sub SortRegion()
    Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Header:=xlYes
End Sub

Sub SortMonth()
    Range("A1").CurrentRegion.Sort Key1:=Range("B1"), Header:=xlYes
End Sub

Sub SortSales()
    Range("A1").CurrentRegion.Sort Key1:=Range("C1"), Header:=xlYes
End Sub

These macros sort on column A, B and C. They can be assigned to command buttons on the sheet.

See the attached demo workbook.

SortProtected.xlsm

[BobH]

Thanks, Hans!

I don't know VBA but quickly ran into a wall trying to use native Excel functions.

[snb]

An intelligent table will do the trick.