Auto task allocation

[Shanthi M]

I am trying to write macro to auto allocate task to the resource when they complete their work

i have task 1 to 100 and 10 team member

3 column task number, user and task status
task 1 john assigned
task 2 ria assigned
task 3 benny assigned and so on till task 10

once if any of the resource completes the task the task status will be changed to completed

Then the immediate available task 11 should get assigned to the resource who marked completed status first

 

[vletm]

Shanthi M
Something like this?
.. select cell to change status

 

[Shanthi M]

Exactly. Brilliant! thank you so much.

I think one more step i need to introduce is form . So not everyone get to see this page which will lead to tampering of data. Should introduce form with User name Task name Task status so they get to see only the current task in the page and when they change the status to complete they get to move to next task. Here User name should be static with those who open the excel and the task gets changed as and when they complete. Let me try that.. So everyone get to see only there task

 

[vletm]

Shanthi M
Is there any reason, why You did You miss that part from You thread?
Why some ... form?
... who open the excel ...
Could You try to explain/write all needed details
- how someone would use this file?
- what for is this file? .. eg if there needs only actual and future tasks then others could ... delete.

I did version 2.
It's userID's are same as previous versions users.

 

[Shanthi M]

I am sorry for the miss not setting the context

I am a Team manager and i have 40 resources under me. i gave the process steps here which i used earlier

Step 1: I create the 1st sheet and input data manually such as
a) task [1 to 1000]
b) assign employees name a to t against the task name
c) capture the status for a to t as assigned

Step 2: Once the team member completes the task (1) they used to ping me offline [microsoft teams] about the completion status.
Step 3: Then i will change the status of task (1) as "completed"
Step 4: Manually look at next available task "u" to the employee .

Challenge: this tracking mechanism is manual process and had to and fro communication. i thought of automating this task allocation workflow.

In the 1st version of file that you shared, still i should track and manage the change on the task status. If i share this file to all 10 member they will open the file in their desktop and by mistake "A" can change the status of "B" and vice versa , also they can tamper the file by deleting or replacing some of the fields.

Change requirement:
the file need 2 version.

the source file [ 1st sheet] will be managed only by me and the employees should be able to see only 2nd version of the file which is the "form" to get the task and save the status and move on to the next. this will be opened by them in there desk. As and when they enter the status the result should be captured in sheet 1 and assigned next task. Once out of task when the task reach to 1000 it should throw "Out of Task".

This will help in Program Managing the project without any manual intervention or without tampering the data and collect the final data without depending on anyone.

in the 2nd version how do i get to see the source file [sheet 1].

Also i am trying to link and upload this in sharepoint as central platform

Thank you so much for your excellent support .

Hope i explained in detail.

Regards,
Shanthi M

 

[vletm]

Shanthi M
Do something missing?
Version 1
> I won't update anymore.
> > and users could only change status once.

Version 2
> except, there is that manual userID-case?
> > which can take away after there are real userIDs.
> what are You linking?
> > did You mention in the beginning something about sharepoint or so?
> > > as well as that there could be many users same time ... ?
> > > > There could need to modify some code ... again ... if that version won't work as it should work.
> You can modify data-sheet and inits-sheet as all other sheets ...
> > How do You would like to get to see those?
> > > eg it would be possible with Your userID.

 

[Shanthi M]

Hi, The second version is perfect .. just that i am not sure how do i get to see the data sheet. I tested the second and it works as expected .. i tested with 2 member of my team in shared folder and its fine. just that i couldn't see the base sheet to add task . get to see the base using my user id will do.

 

[vletm]

Shanthi M
There are two main things which should modify.
#1 userID should be same as which Windows uses ( do You use Windows only?)
#2 I asked ... How do You would like to get to see those?
>>> Do You have any hope/wish or something?

 

[Shanthi M]

Shanthi M
There are two main things which should modify.
#1 userID should be same as which Windows uses ( do You use Windows only?)
#2 I asked ... How do You would like to get to see those?
>>> Do You have any hope/wish or something?

#1 Yes we use only windows
#2 No i dont have any specific idea but was thinking to see the data like version 1. not sure whether thats feasible to have both in one sheet . but only i should view the base file thats the only thing

 

[vletm]

Shanthi M
#1 Okay
#2 ...users cannot select whoever ... or how?
#3 This version works ... this way ... after You've done something
You should open this version

This setting can do once!
Your Windows username should be above userID-text
> Write Your name above UserName-text ( like more realistic name, which can see in user-sheet )
> Close this file
> Open again this file
> You should see same sheet
>> add/modify other users WindowsID and UserNames below userID | UserName
>> Close this file
>>> You can see/modify Data-sheet manually ... but do not mess layout!
>>> Close this file
Ps StatusBar Could show UserID while opening this file.
Only those userIDs can use this file.

 

[Shanthi M]

Hi, The second version is perfect .. just that i am not sure how do i get to see the data sheet. I tested the second and it works as expected .. i tested with 2 member of my team in shared folder and its fine. just that i couldn't see the base sheet to add task . get to see the base using my user id will do.

can you help me understand how to view the datasheet i am seeing only the form.. where to get the data sheet

 

[Shanthi M]

Shanthi M
#1 Okay
#2 ...users cannot select whoever ... or how?
#3 This version works ... this way ... after You've done something
You should open this version
View attachment 80052
This setting can do once!
Your Windows username should be above userID-text
> Write Your name above UserName-text ( like more realistic name, which can see in user-sheet )
> Close this file
> Open again this file
> You should see same sheet
>> add/modify other users WindowsID and UserNames below userID | UserName
>> Close this file
>>> You can see/modify Data-sheet manually ... but do not mess layout!
>>> Close this file
Ps StatusBar Could show UserID while opening this file.
Only those userIDs can use this file.

Getting error Unknown user.

 

[vletm]

Did You follow my given steps from #3?
Did You see anything in Statusbar - left corner?
Note: That the 1st opening can do ONLY one time!

 

[Shanthi M]

while opening

 

[vletm]

Did You wrote those Yourself?
You should see something like in #10 reply.
3rd time
Note: That the 1st opening can do ONLY one time!

 

[Shanthi M]

Did You wrote those Yourself?

In status bar the id was shown so i entered that above the userid

 

[vletm]

... okay ...
Above means
a) something didn't work
b) anyway - You skipped my steps
>> open this file and see --- what is Your ID?
if it not open then send that Your ID here.
(( I'll try to check this next time about one hour later. ))

 

[Shanthi M]

I am sorry to ask you this...version 2 is super cool all that i want to know is from where the data is getting pulled i mean the task ..can i see that sheet where we add all user name and task name and task status

 

[Shanthi M]

... okay ...
Above means
a) something didn't work
b) anyway - You skipped my steps
>> open this file and see --- what is Your ID?
if it not open then send that Your ID here.
(( I'll try to check this next time about one hour later. ))

it showed my id and then immediately had another pop up as user unknown

 

[Shanthi M]

I am sorry to ask you this...version 2 is super cool and perfect.... all that i want to know is from where the data is getting pulled ? i mean the task ..can i see that sheet where we need to add the basic information such as all user name and task name and task status

 

[vletm]

>> open this file and see --- what is Your ID?
Did You read above line?
What did shows between [ ]?
if it not open then send that Your ID here.
Can You send that Your used file here?
After this the 1st opening has gone as here.
It's only You, who could see those tasks and take care users.
or
everybody could do those?
Which You want?

You will need Usernames, which Windows use - only that is valid information.
You can add there as many task numbers
You cannot ... why to modify task statues manually - why?

Open below UN.xlsb -file to get valid UserName and send it (UserName) for me
... if other file won't work with You.

 

[Shanthi M]

i am able to see my user id in []
my user id is shanthmz
attached the file that i used which u gave in # 17
Only i should see the entire task list and others should get only assigned and once completed should get next

i dont want to modify task status.

So here


how do vinoj and jasper will get to know what task i assigned to them
Should i share this excel with all the users to whom i assign task?

Let me try this UN file now

 

[Shanthi M]

i am getting valid username shanthmz in this UN file

 

[vletm]

Shanthi M
Test to open this file.
For me, I can open it with Your username.
It'll ask few question, if You'll open it.
You should see data- & inits-sheets.
Only You can do these.
You can add manually other users information to inits-sheet.
You have to know those information - use UN.xlsb
You can add as many task numbers to data-sheet.
Old datas can clear by button.
You no need to assign any tasks - tasks will assign automatic while user will open this file.

 

[Shanthi M]

Awesome Thanks much will test this .. In case of emergency in my absence if i want to delegate this work to another person how do i do?