include conditional formatting with TRANSPOSE function?

[LizzyJ]

Hi all,

I have transposed some data in excel from one sheet to another but I would like to take the conditional formatting with the data if possible.

Please see attached workbook for reference.

Background:
The data is to illustrate which individuals in an organisation have access to which files on our system. In workbook Sheet 1:
row 1 is row of individuals column A denotes groups or folders. If an individual is a member of the group then "Y" is entered in the cell. The top portion of the spreadsheet is conditionally formatted red if cell is blank and green if the cell contains "Y" to illustrate which individuals are in which group.

Below are the folders. Access to a folder is dependent on being a member of a particular group or groups so OR functions are used to determine if cell for folder access is true or false. The conditional formatting for most rows in this section (all but row 21) is set to green for TRUE and red for FALSE.

However some groups give READ ONLY access to a folder. This is illustrated in row 21. For folder 7 read only access is granted if an individual is a member of group h, full access if a member of group b or group f. Since I can only generate TRUE or FALSE using the OR function for the cell the conditional formatting is based on the cells above.

When i transpose the data (Sheet 2) the formatting is lost. I would like the same formatting on the transposed data (this would still be dependent on the person/group relationship cells). If it was just access or no access without read only this would be easy as I could just recreate conditional formatting of green for TRUE and red for FALSE in the transposed cells but is there a way to maintain the formatting of the transposed cells so that the orange (for read only access) is maintained?


Can this be done? Any help much appreciated!

LizzyJAttachment 338275