Macro or Formula search a merged column for a cell colored yellow and return data from row.

[Logan668]

I need to be able to search under the column of weeks find the yellow marked cell and return the detailed activity to sheet 1.

Date of TIP:

1/2/2017

Date of Week 1 of activity:

1/2/2017

Red

Tactical Implementation Plan (TIP) - Dist Pal 11-13 - Wave #6

TIP Owner:

Asset A

TIP Sponsor:

CIT

<<< Choose the metric used in the TIP

Work Stream

Sub-Work Stream

Sub-Sub-Work Stream

2.1

<<< GAP from Commitment to Business (If Red >> Escalate per standard)

75.9

<<< COMMITMENT to Business (in Midas)

Consider activities for all 3 Bubbles

Week 1

78.0

<<< TIP TARGET - Calculated PSOEE or CIT

Wave to Wave Transition

78

<<< TIP BASELINE PSOEE or CIT for this Wave

13%

87%

0%

Week 1

Week 2

Week 3

Week 4

Week 5

Week 6

Week 7

Week 8

Week 9

Week 10

Week 11

Week 12

Week 13

Week 14

Week 15

Week 16

Week 17

Description & Owner

Target Baseline

Description & Owner

Target Baseline

Description & Owner

Target Baseline

Detailed Workstream PSOEE Impact

#

Detailed Activities

MBC

MI

OS

Owner

Support

Support

Support

2-Jan-17

3-Jan-17

4-Jan-17

5-Jan-17

6-Jan-17

9-Jan-17

10-Jan-17

11-Jan-17

12-Jan-17

13-Jan-17

16-Jan-17

17-Jan-17

18-Jan-17

19-Jan-17

20-Jan-17

23-Jan-17

24-Jan-17

25-Jan-17

26-Jan-17

27-Jan-17

30-Jan-17

31-Jan-17

1-Feb-17

2-Feb-17

3-Feb-17

6-Feb-17

7-Feb-17

8-Feb-17

9-Feb-17

10-Feb-17

13-Feb-17

14-Feb-17

15-Feb-17

16-Feb-17

17-Feb-17

20-Feb-17

21-Feb-17

22-Feb-17

23-Feb-17

24-Feb-17

27-Feb-17

28-Feb-17

1-Mar-17

2-Mar-17

3-Mar-17

6-Mar-17

7-Mar-17

8-Mar-17

9-Mar-17

10-Mar-17

13-Mar-17

14-Mar-17

15-Mar-17

16-Mar-17

17-Mar-17

20-Mar-17

21-Mar-17

22-Mar-17

23-Mar-17

24-Mar-17

27-Mar-17

28-Mar-17

29-Mar-17

30-Mar-17

31-Mar-17

3-Apr-17

4-Apr-17

5-Apr-17

6-Apr-17

7-Apr-17

10-Apr-17

11-Apr-17

12-Apr-17

13-Apr-17

14-Apr-17

17-Apr-17

18-Apr-17

19-Apr-17

20-Apr-17

21-Apr-17

24-Apr-17

25-Apr-17

26-Apr-17

27-Apr-17

28-Apr-17

Safety & Environmental

Tar. = Base =

Housekeeping

Tar. = 80% Base = 0

Description & Owner

Tar. = Base =

1

Hypothesis: If we develop a system to assign key areas to owners, develop standard for area, and LSW, our housekeeping will improve to 80%

2

Define areas to assign owners

X

3

Assign owners

X

X

4

Develop standard for area 1

X

X

5

Develop standard for area 2

X

6

Develop standard for area 3

X

7

Develop standard for area 4

X

8

Develop LSW for area 1

X

9

Develop LSW for area 2

X

10

Develop LSW for area 3

X

11

Develop LSW for area 4

X

12

Go- Live

X

13

Audit developed

X

14

Audit form go-live

X

15

16

<colgroup><col width="136" style="width: 102pt; mso-width-source: userset; mso-width-alt: 4973;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="136" style="width: 102pt; mso-width-source: userset; mso-width-alt: 4973;"> <col width="116" style="width: 87pt; mso-width-source: userset; mso-width-alt: 4242;"> <col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;"> <col width="47" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1718;"> <col width="30" style="width: 23pt; mso-width-source: userset; mso-width-alt: 1097;"> <col width="520" style="width: 390pt; mso-width-source: userset; mso-width-alt: 19017;"> <col width="45" style="width: 34pt; mso-width-source: userset; mso-width-alt: 1645;" span="3"> <col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;"> <col width="104" style="width: 78pt; mso-width-source: userset; mso-width-alt: 3803;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;"> <col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"> <col width="16" style="width: 12pt; mso-width-source: userset; mso-width-alt: 585;" span="7"> <col width="16" style="width: 12pt; mso-width-source: userset; mso-width-alt: 585;"> <col width="16" style="width: 12pt; mso-width-source: userset; mso-width-alt: 585;" span="19"> <col width="16" style="width: 12pt; mso-width-source: userset; mso-width-alt: 585;" span="41"> <col width="16" style="width: 12pt; mso-width-source: userset; mso-width-alt: 585;" span="6"> <col width="16" style="width: 12pt; mso-width-source: userset; mso-width-alt: 585;" span="5"> <col width="16" style="width: 12pt; mso-width-source: userset; mso-width-alt: 585;" span="5"> <tbody> </tbody>

 

[DILIPandey]

search under the column of weeks find the yellow marked cell

Can you give an example ?
What is the structure of sheet 1 ?

Yellow cells can be tracked and detailed activity can be picked but then where to put this output in sheet 1 ?

Regards,
DILIPandey

 

[Logan668]

Detailed activity would need to start at A6 and Index down. Thanks

Week #
Detailed Activities:	Who:	Date:

<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[DILIPandey]

As your TIP sheet is having merged cells, would be difficult to suggest formula and may have reference issues.

Is it possible for you to upload this workbook on a public sharing site after removing sensitive data ? Thx



Regards,
DILIPandey

 

[Logan668]

https://drive.google.com/file/d/0B0lWAiaAcw0aOU1qWVUyUEszUGc/view?usp=sharing