Formula required to copy text from one sheet/cell to another

Hi there,
I need help please; I had worked with excel & compiling formatted worksheets for various tasks up until 5 years ago whereby I had changed my work vocation, the problem is I had noted that my transport manager is using a worksheet without any formatted cells, hence he was doing it the hard way, I piped up that I could offer some assistance and input formulas to help, however I am a bit rusty & for the life of me cannot work out the proper sequence for the following formula.

The following worksheet when I enter a certain name ref in row sheet 1, D9, it will look across the column & copy the text from sheet 1, J9 to a cell in sheet 2 row C22 for the next destination e.g.
D9 – C22 = Sheet 2, J9 (ANDY P)

2nd problem is moving text from any cell in row J whereby the ref name is either Melbourne (1), Sydney (2), Brisbane (3) Nhill (1) or Mildura (1) it looks up the date in cell ref H5 & calculates forward, & transfers the name using the (Bracketed) number as a ref to days to destination,
i.e. All names identified with the 1st ref as a starting point is then copied forward to the respective date in the future, from the 1st Jan to the 2nd Jan, and only using weekday (Mon-Fri) except Saturday, Sunday or Public Holidays.
What I am hoping to achieve is a rolling worksheet similar to a diary whereby a job can be forecasted, and where row C is a driver’s name is automated from a previous delivery either the day before or nominated day/s to arrive by transport i.e.
Adelaide to Melbourne = 1 day, to Sydney = 2 days, to Brisbane = 3 days
Melbourne to Sydney = 1 day, to Adelaide = 1 day, to Brisbane = 2 days
Sydney to Brisbane = 1 day, to Melbourne = 1 day, to Adelaide = 2 days
Brisbane to Sydney = 1 day, to Melbourne is 2 days, to Adelaide is 3 days
I have added comments in sheets 3 & 4, hopefully this will help clarify the issue I have.
Thanks in advance, I honestly appreciate the help, the brain matter isn’t what it used to be.