CODEWITHSUNDEEP
excelspreadsheetconditional-formatting
harry04
harry04

Reputation: 962

Conditional formatting over multiple sheets

I am a bit of an MS Excel amateur, but I'm pretty sure that Excel can handle something like this. I have a spreadsheet where the first sheet is a master sheet presenting the codes and description of some tasks, followed by some (multiple) workrole sheets, each having a small subset of those codes that conform to that workrole.

Master Sheet
Code      |  Description   |  Module 1  |  Module 2  |  Module 3 | ...
 4             XYZ                Yes          No           No
 6             ABC                No           Yes          Yes

Workrole Sheet
Code      |  Module 1      |   Module 2 |  Module 3  | ...
 4
 7

I want that if someone types in Yes in Module 2 corresponding to the Code 6 in the Master sheet, Yes appears in Module 2 of all the workrole sheets that have that particular code 6 in them. Plus, that particular row gets coloured green (but not so important). Can someone help me with this?

Upvotes: 0

Views: 689

Answers (1)

Oliver Leung
Oliver Leung

Reputation: 812

What you want to achieve involves two different actions (just give the most basic way below): 1) To copy the "Yes" to each Workrole sheet:

  • Assume you don't have a very large number of worksheets and codes (otherwise it would be slow)
  • Assume Code is unique.
  • Method: search the same "Code" from Master sheet and copy the corresponding "Module 2" value
  • In each of your Workrole sheet, use =index('Master sheet'![column of Module 2 in master], match([cell contains the Code of your entry], 'Master sheet'![column of Code in master],0))

2) To highlight the row:

  • Select all data row in Workrole sheet, add new conditional formatting rule using formula
  • enter =($[column of Module 2][first data row]="Yes") <-- note the "$"
  • Select the format you want to highlight the rows

Upvotes: 1

Related Questions