Automatic Priority re-ordering possible?

[scmason247]

Hi All,
I am a beginner level Excel user who has recently been experimenting with scheduling abilities in the program. I've been asked by my boss if automatic priority setting is possible as well, and I honestly have no idea. For example,

I would like to be able to automatically update a listing of Projects based on level of importance after one is completed. I have provided a picture to show what I would like to automate. ExcelSchedule screenshot.JPG
Let me know if it's even possible and if so how can it be accomplished...
*willing to move location of list if needed as well.*

Thanks!

[Mumps1]

Can you attach a copy of your file? Also, how do you indicate if a project is complete?

[scmason247]

Here is a copy and I'm not really sure how I'd prefer to represent a completed task. Right now I just have it numbered 1(most important) - #n (least important). Honestly, even if you could just show me how to get it to automatically renumber the tasks starting over from 1 again after one task is deleted (meaning completion), that would work as well. I just need a way of establishing importance. Whatever you think would work. Thank you!LASER_TASK_SCHEDULE_Group_tester.xlsx

[Mumps1]

Just to clarify ... if column L has the number 1 typed into it, you want that row deleted and everything below to move up and re-number. Is this correct?

[scmason247]

Yes, that's right.

[Mumps1]

Try the attached file. When a project is completed, type the word "Complete" in column L to replace the existing priority number and exit the cell. I'm not sure what your command button did, but if it's no longer necessary, you could delete it.

[scmason247]

Mumps,

It didn't seem to work, maybe it's something I'm doing wrong? I attached a screenshot of the result.screenshot.JPG

[Mumps1]

Type the word "Complete" not "Completed". If you want to use "Completed" you can change the line in the code that refers to it.

[scmason247]

Oh, yeah sorry I did do that. I tried both. I changed it to Completed in the code as well.

[Mumps1]

So did it work as you wanted?

[scmason247]

What I sent you previously was the result after I tried all of those. Should have clarified.

[Mumps1]

When I try it, it works perfectly. Are you saying that it still isn't working for you?

[scmason247]

Yeah, I'm not sure why. May just have to admit defeat and figure out another way. Thank you for your help though!

[Mumps1]

That is strange. Are you typing the word "Complete" or "Completed" with the capital "C"? are you getting any error messages? Could I ask you to attach a copy of the file that's not working for you and I'll see how it works for me?

[scmason247]

scmason247.xlsmYup, all of the above. I'm thinking it may just be because I'm opening it on my work computer and marcos or something are not allowed to be enabled. But I'll send you the file to check.

[Mumps1]

It worked for me. If macros are enabled, it's not going to work.

[scmason247]

Oh I see, still no result after I tried that. Could you maybe try doing what you did but delete that command box that's there, its not needed. It won't let me get rid of it on my end, maybe that is the issue?

[Mumps1]

Sorry but in my previous response I should have said that if macros are not enabled, it won't work. In the attachment in this post, I removed the button but that shouldn't make any difference. I tried it again and it worked perfectly. Have you enabled macros in Excel? If you haven't and aren't sure of how to do that, please let me know and I'll post the instructions. If you have enabled macros, try one more thing. Hold down the ALT key and press the F11 key to open the Visual Basic Editor so you can see the macro. Hold down the CRTL key and press the letter "G". This will open up the Immediate Window at the bottom. In this window, type: Application.EnableEvents = True and press the ENTER key. Type "Complete" in column L. If this still doesn't work, I can't see any other reason for the problem. Maybe someone else in this forum with more expertise than I could have another solution. You may want to start another thread attaching your file and explaining the problem you're having. Also include what you have tried and isn't working for you.

[scmason247]

It worked, thank you so much! one more quick question though, is there any chance you can make it so that it only does it for that one column L instead of the row? it deletes the names. If not no problem i'll just retype it!

You're awesome. Boss will love it.

[Mumps1]

I'm not exactly sure what you mean when you say

it only does it for that one column L instead of the row

Do you not want to delete the row? Could you please clarify what you want to delete and what you want to keep? Please refer to specific cells and ranges.

[scmason247]

Basically, is it possible to only automatically order column L, so that when I type in "Complete" only L6-15 or L6- any cell number below 6 are the ones that change? As of now, when I type "Complete" it deletes the whole row across (including the names and everything in the schedule) while it reorders. I'd like the change only to effect the Priority column.

[Mumps1]

See if the attached file works for you.

[scmason247]

Thank you! Works great.

[Mumps1]

My pleasure.