How to Return a value based on a range in a different file or sheet? unsolved

[hemotep]

I Own the tasks of updating the list of tasks in an excel file (let's call it master file). It consists of a column A that has reference numbers for around 15000 tasks and a column B that has the status.

On a weekly basis, different team members will be feeding me with a table (which means another excel file) that contains the update of the status of some of the tasks that I have already in my master file. The file that I'll be receiving will have a column A that has some of the reference numbers and a column B that has the updated status of these reference numbers. So what is going to happen basically is that I'll be receiving an update for 50 tasks out of the 15000 this week and probably 100 next week etc.

What would be the best way to automate copying the status from the files I'll be receiving from the team to the master file? I'm thinking of a formula that can identify the receivedreference numbers and return the value of the status column.



Thanks for your help!

 

[Coding4Fun]

I see 2 options.

1. You could use a VLookup on the task reference number and return the status, this is a bit more manual in that you would need to open both files and then type in the vlookup in a 3rd column and then copy over those values that have been updated.
This is a small example, with your master hsset represented on the left and the users update data on the right. You run a vloopup and it will identify those that are in the users update list along with the status.

2. You could use VBA to parse the users files and update the status in your master file in a more automated way.
Let me know if interested in the VBA approach, you mentioned a formula approach specifically so I did not provide a VBA solution.

 

[hemotep]

Thanks for taking the time to provide such a detailed answer! That's awesome. I'll probably opt for the Vlookup option for now however I'm interested in trying the VBA solution, getting familiar with it then implementing it.