Automatically Drawing Data from Another Worksheet

[AlexNorman100]

Hello,

Please see the attached pictures

As you can see I have different stages involved in planning, advertising, evaluating and rewarding a contract.

I have used conditional formatting to show when a certain stage of the process is in control or not. For example if a stage is completed then I input C and that turns that stage and the next "control" column of the next stage green to highlight that it is control and the next stage can be started. Vice versa if the stage if in progress then the next stages "control" column will turn blue. If the stage is completed but overdue then the next stages "control" column will turn red to highlight that the next stage is out of control.

The second picture (Names) highlights the contract name and this will color red, yellow or green according to the latest control of its latest stage in the process. For example, if the evaluation stage is in progress (IP) then the contract name will turn yellow to show it is in progress. A contract name will only turn green once the final stage of the process is complete.

Basically what I want to be able to do is for excel to automatically show (on another worksheet) all contacts that are at a certain control level. For example I want a worksheet which shows all contracts that are in progress, another worksheet which shows all contracts completed and another worksheet which shows all contracts that are out of control (i.e. a stage is overdue). From this I can make comments on each section. I want this to automatically update so that if a contract that was in progress is now complete it moves to the the worksheet showing all completed contracts whilst taking with it the comments.

I hope this makes sense??

Many Thanks for your help in advance!!
Alex

[AliGW]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[AlexNorman100]

Attached is the workbook.

The Contracts Register is where all the conditional formatting is.

Basically I want all the contract names that are yellow to automatically show in the "In Progress Contracts" worksheet, all the contract names that are green to automatically show in the "Completed Contracts" worksheet and all the contract names that are red to show in the "Out of Control Contracts".

Hope this helps?

[AliGW]

OK - yes, there are a lot of CF rules, aren't there?!

So, in order to be able to extract the lists you want, please list here the rules you are using for the colour coding, then the same criteria can be used to extract the lists you need.

[AlexNorman100]

Okay so here are the CF rules that I have used in order for implementation:

Firstly - Existing Contract End Date
- When a contract is 18 months away from ending the process (which starts to the far right of the worksheet) must begin
- Contract Expired
- Seriously overdue from Process Start Date (the 18 months)
- 1 week to 1 month overdue from Process Start Date (i.e. 17 months to 18 months -1 day)
- 1 week overdue from Process Start Date
- Exactly 18 months away
- Between 1 week away from Process Start Date (i.e. 18 months to 18 months and 1 week away)
- Between 1 week and 1 months away from Process Start Date
- You get the picture...

Secondly - the six stages of new contract process (when a contract is 18 months away from ending the process must begin)
- So when a contract is yellow (exactly 18 months away) or light blue (1 week away from Process Start Date) then I know to begin the process
- In the status column I put either: C, IP, IP/O, O/NIP or CBO
- The date of that particluar stage and the control column of the next stage will then turn either green, blue, amber or red based on the status.
- i.e. if I put the status as IP then that stages date will turn yellow and the control of the next stage will turn blue.

Thirdly - Contract Name
- Once every stage of the process has been completed then the Contract Name (Contract 1, 2, 3, 4, 5 etc) will turn green.
- If only the first stage has been completed then the Contract Name will turn yellow to show that it is in progress
- If the latest stage of the process is out of control then the Contract Name will turn red to show it is out of control

That is the order of the ruling.

So I am looking to automatically extract the data from the Contract Name into the other worksheets based on whether they are green, yellow or red. Can I do this the same way I have been doing the previous CF?

Thanks for your help btw!

[AlexNorman100]

I tried to copy and paste the specific formula but it wouldn't let me and would block my post :/

[AliGW]

Try again, making sure you add spaces either side of the < and > characters. It's the ones you use to change the contact name formatting that we need to work with - they will be used in the formula to extract the lists, not conditional formatting.

[AlexNorman100]

Okay will reply with this tomorrow - thanks for your help btw

[JeteMc]

This proposed solution uses a helper column (AM) on the Contract Register sheet populated by the formula:

Please Login or Register  to view this content.

The formulas, like the one for out of control contracts that follows, then populate column A of the status sheets reference the value in column AM:

Please Login or Register  to view this content.

Let us know if you have any questions.