sort ranges of data based on order of values in a list

[Doug Mutzig]

Good morning all! I received a request for help that has me stumped and I am hoping someone can offer some guidance on what to do.

The end user has created a worksheet that has several data entry cells (see picture). The first entry for each section is the room number which comes from a drop down selection list. Since patients can be placed in any room at any time they would like to be able to sort all of the sections by room number. I believe they want only rooms with a patient to be sorted and empty rooms moved to the bottom. There are no tables for these sections, but the ranges are static. Is there a way to review all of the input data by range and then sort the ranges by room number and name (something entered vs. blank)?

I am thinking that a change to an input form that enters the data into a table and then somehow displays that table contents in the format shown would be a better way to do, I just do not know how to get the data from that table to the format shown. I know it could be as easy as linked cells; however, I am wondering if there is a better way?

 

[mamady]

Hi Dear,

Since this will create a database, yes, having one input sheet that logs those records as a table is a better setup. However, this will require VBA codes.
Instead, I recommend utilising MS Access. There many tutorials on youtube on how to create a form and querying the database.

Best Regards
M. Yusuf

 

[Doug Mutzig]

Hi Mamady! Thank you very much for the quick response. I had thought of access as it seems perfect for this, but our organization discourages the use of access or "rogue" databases so I have have to work with excel (even though the same underlying issue is there, i.e. someone leaves and no one knows how to edit/fix it).

 

[Redders]

Hi @Doug Mutzig, I would maybe request a change within your company for this to be implemented into the main business system if they discourage the use of working off system, it sounds like you have a fairly decent business case for the requirement