. . . 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.
Sorting - Is It Possible . . .
-
- UraniumLounger
- Posts: 9262
- Joined: 13 Feb 2010, 01:27
- Location: Deep in the Heart of Texas
Sorting - Is It Possible . . .
Bob's yer Uncle
Dell Intel Core i5 Laptop, 3570K,1.60 GHz, 8 GB RAM, Windows 11 64-bit, LibreOffice,and other bits and bobs
(1/2)(1+√5) |
-
- Administrator
- Posts: 78378
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sorting - Is It Possible . . .
This requires some VBA code.
1) In the ThisWorkbook module:
This protects the sheet with password "BobH" but allows VBA code to be run on the protected sheet.
2) In a standard module:
These macros sort on column A, B and C. They can be assigned to command buttons on the sheet.
See the attached demo workbook.
1) In the ThisWorkbook module:
Code: Select all
Private Sub Workbook_Open()
Worksheets("Sheet1").Protect Password:="BobH", UserInterfaceOnly:=True
End Sub
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
See the attached demo workbook.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- UraniumLounger
- Posts: 9262
- Joined: 13 Feb 2010, 01:27
- Location: Deep in the Heart of Texas
Re: Sorting - Is It Possible . . .
Thanks, Hans!
I don't know VBA but quickly ran into a wall trying to use native Excel functions.
I don't know VBA but quickly ran into a wall trying to use native Excel functions.
Bob's yer Uncle
Dell Intel Core i5 Laptop, 3570K,1.60 GHz, 8 GB RAM, Windows 11 64-bit, LibreOffice,and other bits and bobs
(1/2)(1+√5) |
-
- 4StarLounger
- Posts: 574
- Joined: 14 Nov 2012, 16:06
Re: Sorting - Is It Possible . . .
An intelligent table will do the trick.