Pull data from a tab based on conditional formatting

bdavids3

Board Regular
Joined
Feb 6, 2006
Messages
55
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>
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,141,842
Messages
5,708,919
Members
421,598
Latest member
NewHere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top