Pull data from a tab based on conditional formatting

[bdavids3]

I have a tab called Master with the following columns:

• Program Director
  Clarity ID {conditionally formatted}
  Project Name
  Status
  Start
  Baseline {conditionally formatted}
  Finish {conditionally formatted}
  Phase {conditionally formatted}
  Tier {conditionally formatted}
  PM
  Approved Funding {conditionally formatted}
  Forecast {conditionally formatted}
  Variance % {conditionally formatted}

Master tab sample data:
<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="0"><tr><th bgcolor="#00CCFF">Program Director</th><th bgcolor="#00CCFF">Clarity ID</th><th bgcolor="#00CCFF">Project Name</th><th>Status</th><th bgcolor="#00CCFF">Start</th><th bgcolor="#00CCFF">Baseline</th><th bgcolor="#00CCFF">Finish</th><th>Phase</th><th>Tier</th><th bgcolor="#00CCFF">PM</th><th bgcolor="#00CCFF">Approved Funding</th><th bgcolor="#00CCFF">Forecast</th><th bgcolor="#00CCFF">Variance %</th></tr><tr><td>Johnson, Sarah</td><td>IT2005P00171</td><td>IT: Switch Upgrade</td><td>R</td><td>9/27/2005</td><td>9/29/2006</td><td>9/30/2006</td><td>Implement</td><td bgcolor="#FFCC99">3</td><td>Doe, John</td><td>$40,986</td><td>$57,184</td><td bgcolor="#FF0000">-39.52%</td></tr><tr><td>Johnson, Sarah</td><td>IT2006P00031</td><td>IT: Email Upgrade to Version 8</td><td>G</td><td>12/20/2005</td><td>6/29/2007</td><td>6/29/2007</td><td>Implement</td><td bgcolor="#FFCC99">1</td><td>Doe, John</td><td>$401,000</td><td>$136,418</td><td bgcolor="#FF0000">65.98%</td></tr><tr><td>Johnson, Sarah</td><td>IT2006P00032</td><td>IT: Web Server Upgrade to Version 8</td><td>G</td><td>1/1/2006</td><td>3/2/2007</td><td>3/2/2007</td><td>Implement</td><td bgcolor="#FFCC99">1</td><td>Doe, John</td><td>$401,000</td><td>$88,016</td><td bgcolor="#FF0000">78.05%</td></tr><tr><td>Johnson, Sarah</td><td>IT2006P00165</td><td>IT: Disaster Recovery</td><td>G</td><td>6/20/2006</td><td>12/1/2006</td><td>12/1/2006</td><td>Solution Scoping</td><td bgcolor="#FFCC99">3</td><td>Doe, John</td><td bgcolor="#99CC00">   </td><td>$46,781</td><td>   </td></tr><tr><td>Johnson, Sarah</td><td>IT2006P00166</td><td>IT: COBOL Elimination</td><td>G</td><td>6/20/2006</td><td>12/29/2006</td><td>12/29/2006</td><td>Solution Scoping</td><td bgcolor="#FFCC99">2</td><td>Doe, John</td><td>$75,600</td><td bgcolor="#CC99FF">   </td><td>   </td></tr><tr><td>Johnson, Sarah</td><td>IT2006P00006</td><td>IT: Firewall Upgrade</td><td>G</td><td>12/19/2005</td><td>8/31/2006</td><td>9/5/2006</td><td>Implement</td><td bgcolor="#FFCC99">2</td><td>Smith, Joe</td><td>$870,433</td><td>   </td><td>   </td></tr></table>

I have another tab called Key that tells what each of the colors used on the Master tab are. This tab is a single column with 10 rows, 1 for each color. The text description is filled with the corresponding color used on the Master tab.

Key tab data:
<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="0"><tr><td bgcolor="#FFFF00">Status not filled out</td></tr><tr><td bgcolor="#FF00FF">Past Initiate & no baseline</td></tr><tr><td bgcolor="#FFCC00">Status=G and shouldn't be</td></tr><tr><td bgcolor="#008080">Tier 3 & still in Initate phase</td></tr><tr><td bgcolor="#FF8080">Baseline/Finish date past due</td></tr><tr><td bgcolor="#CCFFFF">Still in Initiate after >1 month</td></tr><tr><td bgcolor="#FFCC99">Incorrect tier</td></tr><tr><td bgcolor="#99CC00">Past Initiate & no Approved Funding</td></tr><tr><td bgcolor="#CC99FF">Past Initiate & no Forecast</td></tr><tr><td bgcolor="#FF0000">Variance +/- 10%</td></tr></table>

I have a final tab called Summary where I would like to pull only a couple of the tabs from Master where columns have been conditionally formatted. The columns I would like to pull are:

• PM
  Clarity ID
  Project Name

Plus I'd like to add an additional column called Comments that puts the text from the Key. The relationship is 1 row on the Summary tab to many possible conditional formats (noted above which have conditional formatting).

Sample data from Summary tab:
<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="0"><tr><th>PM</th><th>Clarity ID</th><th>Project Name</th><th>Comments</th></tr><tr><td>Doe, John</td><td>IT2005P00171</td><td>IT: Switch Upgrade</td><td>Variance > +/- 10%</td></tr><tr><td>Doe, John</td><td>IT2006P00031</td><td>IT: Email Upgrade to Version 8</td><td>Variance > +/- 10%
Status=G and shouldn't be</td></tr><tr><td>Doe, John</td><td>IT2006P00032</td><td>IT: Web Server Upgrade to Version 8</td><td>Variance > +/- 10%
Status=G and shouldn't be</td></tr><tr><td>Doe, John</td><td>IT2006P00165</td><td>IT: Disaster Recovery</td><td>Past Initiate & no Approved Funding
Status=G and shouldn't be</td></tr><tr><td>Doe, John</td><td>NS2006P00166</td><td>IT: COBOL Elimination</td><td>Past Initiate & no Forecast
Status=G and shouldn't be</td></tr><tr><td>Smith, Joe</td><td>IT2006P00006</td><td>IT: Firewall Upgrade</td><td>Baseline/Finish date past due
Past Initiate & no Forecast
Status=G and shouldn't be</td></tr></table>