Cell format from value in different sheet

[Urlord]

I have a workbook where I would like to highlight cell A1 in sheet one if any cell in column d on sheet 2 is not blank.

Thanks

 

[Sharpedge]

I have two worksheets. The first is a list of tasks that have to be completed from Jan to Dec. The second sheet breaks up the tasks into a list for each month. I am trying to set up the CF for a cell based on the date the task is completed in the cells in sheet 2. I have named the cells as indicated above. What I want is the cell in sheet 1 to be yellow if the named cells are blank and green if all the cells have an entry date for the particular month. If a task is not completed by the end of the month the cell in sheet one would turn red or if a date beyond the month is entered. For example if you complete the task in February for January the cell would be red or if you complete all the tasks but one.

Thanks for your assistance

 

[T. Valko]

I have two worksheets. The first is a list of tasks that have to be completed from Jan to Dec. The second sheet breaks up the tasks into a list for each month. I am trying to set up the CF for a cell based on the date the task is completed in the cells in sheet 2. I have named the cells as indicated above. What I want is the cell in sheet 1 to be yellow if the named cells are blank and green if all the cells have an entry date for the particular month. If a task is not completed by the end of the month the cell in sheet one would turn red or if a date beyond the month is entered. For example if you complete the task in February for January the cell would be red or if you complete all the tasks but one.

Thanks for your assistance

Sorry, not following you on this.

Can you post some sample data and tell us what results you expect?

 

[Sharpedge]

Thank you for responding.

Sheet one is a master list of all tasks that need to be completed from Jan - Dec for example Cell A2 is labeled Equipment Inspections with Cells B2:M2 represent Jan - Dec.

Sheet two is the list of each one Equipment Checklsit of which there is 20 checklists as these are completed during the month the inspector would list the date completed.

When I review sheet one I wanted the cells for that specific month like January be yellow if no entry had been made on sheet two or only part of them are done, green if all the equipment had been checked during the month and red if equipment was missed and we are in the next month or an entry that is made with a date beyond the current month.

I appreciate your input.

Thanks

 

[T. Valko]

Thank you for responding.

Sheet one is a master list of all tasks that need to be completed from Jan - Dec for example Cell A2 is labeled Equipment Inspections with Cells B2:M2 represent Jan - Dec.

Sheet two is the list of each one Equipment Checklsit of which there is 20 checklists as these are completed during the month the inspector would list the date completed.

When I review sheet one I wanted the cells for that specific month like January be yellow if no entry had been made on sheet two or only part of them are done, green if all the equipment had been checked during the month and red if equipment was missed and we are in the next month or an entry that is made with a date beyond the current month.

I appreciate your input.

Thanks

I would need to see some data to get a better understanding of what you want to do.

Is it possible that you could post a link to a SMALL sample file?

 

[Sharpedge]

This is the Master list where the cells would by color indicate the progress of the inspection process.
[TABLE="width: 218"]
<colgroup><col style="width: 98pt; mso-width-source: userset; mso-width-alt: 3726;" width="131"> <col style="width: 60pt;" span="2" width="80"> <tbody>[TR]
[TD="class: xl65, width: 131, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 80, bgcolor: transparent"]Jan[/TD]
[TD="class: xl65, width: 80, bgcolor: transparent"]Feb[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Equip. Checklists[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


This is the sheet in which the inspector would list when they completed the checklist.

[TABLE="width: 218"]
<colgroup><col style="width: 98pt; mso-width-source: userset; mso-width-alt: 3726;" width="131"> <col style="width: 60pt;" span="2" width="80"> <tbody>[TR]
[TD="class: xl65, width: 131, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 80, bgcolor: transparent"]Jan[/TD]
[TD="class: xl65, width: 80, bgcolor: transparent"]Feb[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 131, bgcolor: transparent"]Bldg 201 Heating/Cooling Checklist[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl66, width: 131, bgcolor: transparent"]Bldg 202 Heating/Cooling Checklist[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

An actual date of completion is entered.

My goal is to be able to open the workbook look at the Master sheet and know if we are completing the checks. I have 32 entries on the master list and in the case of the Equipment Checklist I have 16 to be completed each month, 32 each quarter. If I colored the cells seemed like a good visual aid before you would drill down into the other sheets.

Thanks

 

[T. Valko]

This is the Master list where the cells would by color indicate the progress of the inspection process.
[TABLE="width: 218"]
<TBODY>[TR]
[TD="class: xl65, width: 131, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 80, bgcolor: transparent"]Jan
[/TD]
[TD="class: xl65, width: 80, bgcolor: transparent"]Feb
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Equip. Checklists
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]


This is the sheet in which the inspector would list when they completed the checklist.

[TABLE="width: 218"]
<TBODY>[TR]
[TD="class: xl65, width: 131, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 80, bgcolor: transparent"]Jan
[/TD]
[TD="class: xl65, width: 80, bgcolor: transparent"]Feb
[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 131, bgcolor: transparent"]Bldg 201 Heating/Cooling Checklist
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 131, bgcolor: transparent"]Bldg 202 Heating/Cooling Checklist
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

An actual date of completion is entered.

My goal is to be able to open the workbook look at the Master sheet and know if we are completing the checks. I have 32 entries on the master list and in the case of the Equipment Checklist I have 16 to be completed each month, 32 each quarter. If I colored the cells seemed like a good visual aid before you would drill down into the other sheets.

Thanks

Let's see if I understand...

Equip. Checklists is 1 of 32 categories listed on the Master sheet.

These are items that correspond to Equip. Checklists:

Bldg 201 Heating/Cooling Checklist
Bldg 202 Heating/Cooling Checklist

If this is correct then how do we know that the above items are related to the category? If we need to look for the items in the Equip. Checklist category what do we look for?

 

[Sharpedge]

Thank you for replying to my question.

I was only looking at this from opening the master sheet, even though entries would be made on the sheet with the Equip. Checklist which I have named the sheet Equip. Checklist. But you absolutely right Regardless of how I would attempt to flag the status on the master sheet I would ultimately review the subsequent Equip. Checklist to see exactly what is done or what isn't done. It would be better to format those cells based on the date entered.

Thank you again.

 

[T. Valko]

Thank you for replying to my question.

I was only looking at this from opening the master sheet, even though entries would be made on the sheet with the Equip. Checklist which I have named the sheet Equip. Checklist. But you absolutely right Regardless of how I would attempt to flag the status on the master sheet I would ultimately review the subsequent Equip. Checklist to see exactly what is done or what isn't done. It would be better to format those cells based on the date entered.

Thank you again.

I still can't envision what your setup might look like so at this point I would need to see it to be able to make a suggestion.

 

[Sharpedge]

To give you some more back ground I am a Safety Manager and the Master List is the items that I must complete within the year. One is Equipment Checklsit for each piece of equipment. My example was poorly thought out. The equipment is Tow Boats in which I have 20 per month that must be inspected based on a checklist that covers various aspects of the vessel from the safety equipment to the equipment used by the crew. The master list also has fire drills as one must be completed by each crew every month and safety drills by every crew quarterly. I have a vessel inspection sheet where I have listed each vessel and then along the columns the months. I also have sheet to track the drills setup similarily to the inspection. What I was trying to achieve was to enter the dates these inspections and drills take place on their repsective sheets and then on the Master sheet have a single cell representing the month of January for vessel inspections. If all were completed by months end that cell would be green, if none or only some had been done then it would be yellow, and if we entered any the next month or had missed any during the month it would be red. As you pointed out I would go to the individual sheet and maybe that is really all i need. I certainly appreciate your input.

 

[T. Valko]

To give you some more back ground I am a Safety Manager and the Master List is the items that I must complete within the year. One is Equipment Checklsit for each piece of equipment. My example was poorly thought out. The equipment is Tow Boats in which I have 20 per month that must be inspected based on a checklist that covers various aspects of the vessel from the safety equipment to the equipment used by the crew. The master list also has fire drills as one must be completed by each crew every month and safety drills by every crew quarterly. I have a vessel inspection sheet where I have listed each vessel and then along the columns the months. I also have sheet to track the drills setup similarily to the inspection. What I was trying to achieve was to enter the dates these inspections and drills take place on their repsective sheets and then on the Master sheet have a single cell representing the month of January for vessel inspections. If all were completed by months end that cell would be green, if none or only some had been done then it would be yellow, and if we entered any the next month or had missed any during the month it would be red. As you pointed out I would go to the individual sheet and maybe that is really all i need. I certainly appreciate your input.

Ok, I think we can do what you want but I would need to see the file to figure it out.

You seem to be reluctant to post a file but without it I don't think I can help you. I have to understand what you want to do before I can help!