Re: Order Columns & Tabs Based On User Input
You cannot re-order Columns, but you can sort cells in them. So, other that Ascending or Descending... you would need a Custom Sort.
To sort Sheets see Sort Excel Worksheets/Sheets in Excel
Re: Order Columns & Tabs Based On User Input
QuoteHow would I reorder columns & tabs based on a form where the user can put them into order that they would like the columns?
As Dave mentioned, you can't change the order of the columns, you can only sort the cells in them.
One idea you might explore is to use a hidden header row spanning the number of columns in the sort range. The cells on this row will receive a value based on the user selection per below and must be located in the same columns as the real headers.
On a different worksheet, set up data validation, list option, in an equal number of cells and let the user pick the headers (using the real names).
Next, match the selected header from the "pick" sheet to the real header on the data sheet then offset to the adjacent cell in the hidden header row to return a number.
Example: user picks "header 3" as the first column - return "1" in to the hidden cell in the header row, etc.
Then use the Sort Option Left-to-Right with the hidden header row as the sort key
Don't have time just now to develop an example, but this should get you going and it will be dynamic for chosen the sort order. You will have to develop either code or formula to prevent selection of duplicate headers in the "pick sheet."
Re: Order Columns & Tabs Based On User Input
Guy, why not detail what you final aim is, perhaps there are better ways.
Re: Order Columns & Tabs Based On User Input
Quote from Dave HawleyGuy, why not detail what you final aim is, perhaps there are better ways.
Dave, Thanks. The final aim is to make my spreadsheet "totally" customizable. Some users like to have the column which contains "Distance" second and "Time" third. I want them to be able to reorder them even though the spreadsheet is locked (in other words they can't do it manually). Also, I have so many tabs that the user has to scroll to see them all. Many users would like to reorder the tabs so they can have the most used tabs on the left.
Warm regards,
Guy
Re: Order Columns & Tabs Based On User Input
You could create some Custom Sort options and then simply have a Macro that fires off the one the user chooses. Being protected wont effect Macros if you use UserInterfaceOnly with Protection.
For ease of use in a Workbook with many sheets, see Create a Sheet Index of Excel Worksheets
Don’t have an account yet? Register yourself now and be a part of our community!
