Transposing data between tables in Excel

[DunglisonA]

I want to be able to automatically transpose data from Table A (in the example) into Table B.

The data in the example is fabricated and not real-world (for your info)

When doing rosters we populate according to the modality/shift time. (with the modality/time) in the first column and the staff member initials under the date. That way I know all the modalities are covered with staffing.

However, I then need to present the modality roster to the staff and this needs to be in the format of the staff in the first column and their shifts presented in date order.

Is there a means so I only fill in staff initials into Table A and Table B automatically populate with the appropriate details??

Please see the example I have uploaded for this query.

Regards

A

[Kaper]

in B12:

Please Login or Register  to view this content.

copy down and right.
Note that some cell addresses are mixed, and some absolute in the formula

If you want colors in cells use conditional formatting (in Styles group of Home ribbon) -> Highlight Cell Rules - > Equal to -> Custom formatting , so for value in cell equal 07.00-15.00 [ED] set interior color green, etc.

I did first two conditional formattings in the attached file