Link cells/columns/rows with values to another worksheet

[Morgan]

Hello. I have a worksheet that project team members use to identify the
access rights different job positions will have for various tasks
within a client-server application.

Across the top in B1:F1 are the tasks. Down the left side in A2:A5 are
the job positions. Cells B2:F5 are where the access rights are
identified.

An example of my Sheet1 is:


Code:
--------------------
1: create w/o approve w/o req.material purch. mat'l sched. wrk
2:all users read-only update read-only read-only
3lanner update update
4:buyer update
5:superv update
--------------------


This is great for identifying the requirements, but not so great for
the people entering the data into the application (you define the tasks
on one screen, the job positions on another, etc.).

What I would like to do is take (link) the data from Sheet1, and put it
into columns on Sheet2 similar to below:


Code:
--------------------
1: all users planner buyer superv
2: create w/o read-only create w/o update purch. mat'l update approve w/o update
3: req.material update sched. wrk update
4: purch. mat'l read-only
5: sched. wrk read-only
--------------------


I've got Sheet2, row 1 setup to populate the job positions using:
{=TRANSPOSE(Sheet1!A2:A5)}

What I'm having problems with is linking the tasks and access rights,
removing/skipping blank cells, and putting the data into the columns
under the appropriate job function. I'm at a loss as to how to do
this, but imagine it would involve separate functions/code determining
the task and access rights, and then concatenating them with an '&'.

I would prefer to use an Excel function, since that would update Sheet2
on the fly. But if that would be too complicated, I could use a
macro.

Any assistance would be greatly appreciated. Thank you.