Joanna has attended:
Excel Intermediate course
Sorting & Filter
Hi there,
I was wondering if you could help me an invite sheet. We are often sending invites to a number of Guests, so typical sheet would have guests names, company, guest's position, email, PA's email, date invite was sent and RSVP with 3 coloured options: accepted-green, declined-red and pending-orange. We do not send all invites at once and sometimes list can be long. So I was wondering how to set up the sheet with names in alphabetical order, but then also sorted alphabetically. So if XYZ changes status from none invited to pending, it will be automatically placed within the orange sector in alphabetical order within orange sector. In addition, what would be the best way to set the colours depending on answer?
I hope above makes sense. I look forward to your helpful reply.
Kind regards,
Joanna
RE: Sorting & Filter
Hello Joanna,
Hope you enjoyed your Microsoft Excel Intermediate course with Best STL.
Thank you for your question regarding Sorting and Filtering.
I have created and attached a sample sheet containing a few things which may help with your enquiry.
I have used data validation to add a drop-down list to select either Accepted, Declined, Pending or Not Yet Invited.
I have sorted by colour with green at the top followed by yellow, then red and finally by guest name in alphabetical order. The file has a macro to resort after each change and you only have to use the shortcut of CTRL + m.
Give this a try and let me know if it helps.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
Attached files...
RE: Sorting & Filter
Hi Rodney,
Thank you for your reply and template. This is exactly what I am after. However when I open document, it asks me to Enable content, which I do, and when I want press ctrl+m, it comes up as an error "Runtime error 9" Subscript out of range and options to End, Debug or Help. How should I enable the macro?
Thank you in advance.
Kind regards,
Joanna
RE: Sorting & Filter
Hello Joanna,
It's possible that the shortcut may not work. I have changed the code a bit to refer to whatever the active sheet is rather than Sheet1. This makes the code more open for use.
Try to run the code by going to the right-hand end of the View ribbon and click the Macros button and then click View Macros. The macro called Sort_RSVP should be there and already selected. Click the Run button. If the macro still gives an error then click end.
The file I have now attached has the same code but with some changes. Try this one out as well. If you want to check if there is a shortcut key assigned to the macro then click the Macros button again and click the Options... button. If the little box is empty then simply enter a lowercase m into the shortcut box and close the dialogue box. Then try CTRL + m, hopefully this time it will work.
If this doesn't work then I suggest you remove the macro altogether and re-record it again.
Give this a go and let me know how you get on.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
