Back some 30 years ago, I was changed with trying to resurrect the local beekeepers association which had been dormant for some 20 years of so. My first need was a clever way to keep an updated roster that could be passed around at meetings so that attendees could put a check mark by their name instead of "signing in". I also need it to be easy to maintain. So here's what I cam up with.
I developed a roster in Excel that was in alphabetical order by last name with a blank line below each name so that anyone needing changes in their information could make them immediately under their name. That served my needs quiet well but was a little difficult to maintain because of the blank lines. As is obvious, any list with blank lines can not be sorted without moving all of the blank lines to the end of the list and all of the pertinent data at the beginning without the blank lines so I set about "fixing" that problem.
Here's what I came up with. The new roster was set up in one worksheet (entitled Roster) of a 2 worksheet workbook. The other worksheet was for data entry only and is entitled "sorted". To use the Roster, data is entered in the "sorted" worksheet, with no blank lines, in any order. When all data was entered or anytime sorting is desired, the entire "sorted" worksheet is sorted by any column, usually the last name (but can be by any column).
The data is automatically transferred into the "roster" worksheet, in the order sorted, WITH the blank lines between each entry.
No data is ever entered manually in the "Roster" worksheet because that would replace the formulas that do the work. To prevent this from accidentally happening, the "Roster" is protected so that no data can be entered. The password is listed at the bottom of the "Roster" so that anyone wanting to intentionally make changes to the formulas can do so.
To summarize; enter all new information in the "sorted" worksheet, then do a "data sort" and the information will automatically transfer to the "Roster" worksheet WITH THE BLANK LINES FOR CHANGES. That's it!
Oh, by the way I have loaded the first few lines with my information with the first letter of my last name changed for sorting purposes. You will need to go to the "sorted" sheet and highlight all of the information (not including the column headings - ie just my name and related information) and right click and select "Clear Contents" and then start entering your data. I would suggest enter 3 lines maybe and check see if all is well if not let me know. I would rather not face your wrath if you entered a full sheet(s) of data and it didn't work for some reason! You might consider first changing various information in the data I have entered, sort it and see how it works.
Give it a try and let me know if you have any problems. Feel free to email me if you have questions no matter how trivial.
Bob Fanning, k4vb