Dynamic means it changes when you change any of the data  items without you
needing to tell it to run a macro. I will look at it as a static (manual
update)

Can I assume that when you have imported your sheet it will have in each row
(apart from blank rows)

In Column A, the project  name
In Column B, the design  stage
In Column C, the start  week in the range 1-52
In column D, the duration in weeks

There is a blank line after each project

There is a heading which will be in row  1
There will be a blank row on row 2
The data starts on row 3

Each project has six stages as shown below. The names of these stages are
exactly as shown and will never change.

Will any of the stages ever be missed, if so how will this appear in the data
imported from MSProject?

What you want is a spreadsheet showing on each row:

In Column A the Project name
In Columns B to BA (I think that is 52 columns, but lets assume it is - anyhow
the next 52 columns) colours indicating which stage is planned for those
weeks.

When you confirm this, or let me know of any changes I will make a macro for
you. That macro will need to be run each time  you import  the data (once a
week)

Project1 Design 26 1
Project1 Install Hardware 37 4
Project1 Install Software 41 4
Project1 Configure 49 1
Project1 Prep 50 2
Project1 Support 52 1