Conditional Format Based on Week Input

[KrisWain320]

Hey All,

I was wondering if there was a way to conditionally format a cell based on if a certain row of information has been entered.

Put in more layman terms. I have a workbook which has a master sheet filled in daily. On this sheet I have a cell which I manually change it's background colour based on if a calibration has been run on a certain machine.

On another tab in this workbook i have a table where the results of said calibration are recorded against the week it was ran.
For example:

[TABLE="width: 929"]
<tbody>[TR]
[TD][/TD]
[TD]Low Tol[/TD]
[TD]High Tol[/TD]
[TD]Z[/TD]
[TD]DV[/TD]
[TD]X[/TD]
[TD]DV[/TD]
[TD]Y[/TD]
[TD]DV[/TD]
[TD]Z[/TD]
[TD]DV[/TD]
[TD]Y[/TD]
[TD]DV[/TD]
[TD]Z[/TD]
[TD]DV[/TD]
[/TR]
[TR]
[TD]Week 1[/TD]
[TD]-0.15[/TD]
[TD]0.15[/TD]
[TD]1773.05[/TD]
[TD]0.05[/TD]
[TD]1105.08[/TD]
[TD]0.08[/TD]
[TD]423.89[/TD]
[TD]-0.11[/TD]
[TD]1773.01[/TD]
[TD]0.01[/TD]
[TD]487.94[/TD]
[TD]-0.06[/TD]
[TD]1773.57[/TD]
[TD]0.05[/TD]
[/TR]
</tbody>[/TABLE]


Is there a way to set up a conditional formatting on the calibration cell that checks the current week number and then checks if the row for that week has been completed. If the data has not been input then the cell shows red. If the week's data has been input the cell shows green?

I've been trying to figure out how to do this without inputting a macro as i would like to avoid inserting another button into my worksheet for this action. The best i thought of was to generate an if statement in another cell that displays "yes" or "no" and then conditionally format based on this but i am unsure how to write said if statement.

Any help greatly appreciated.

Many Thanks
Kris

 

[Little_Clubber]

This is definitely possible but explaining it can be a little tricky especially when I'm assuming a lot of cell references.

For this, I'm going to assume that in your example above, the machine calibration data is in a Sheet called "MC1" and "MC2" and "MC3" etc.
I'm also assuming that Week 1 from your example above is in cell A2, Low Tol is in cell B1, High Tol in B2 etc. Your Z DV ends in O1
In your "Master Sheet", I'm going to assume "Week 1" is also in Cell A2, Week 2 in A3 and so on, and from B1, D1, E1, etc are the machine names which exactly match the names of each sheet.


So in the master sheet, in B1 is simply MC1 and cell A2 is Week 1; therefore B2 is where we want to know if the machine has had calibration data entered.

In B2, enter the following formula

=IF(VLOOKUP($A2,INDIRECT("'"&B$1&"'!A:O"),15,FALSE)="","No","Yes")

This looks at the week num in the Master, finds the relevant sheet based on the machine name, looks up the week and looks for data entered in Z DV (Column O) against that week.


Copy this formulae Down and then across, then do a conditional formula where "Yes"=Green and "No" = Red

You will have a lot of Red No's unless you have a date on your spreadsheet somewhere and build =today() into your formula.

If this doesn't make sense to you, feel free to send me the file and I'll sort it.
littleclubber@gmail.com

 

[KrisWain320]

This is definitely possible but explaining it can be a little tricky especially when I'm assuming a lot of cell references.

For this, I'm going to assume that in your example above, the machine calibration data is in a Sheet called "MC1" and "MC2" and "MC3" etc.
I'm also assuming that Week 1 from your example above is in cell A2, Low Tol is in cell B1, High Tol in B2 etc. Your Z DV ends in O1
In your "Master Sheet", I'm going to assume "Week 1" is also in Cell A2, Week 2 in A3 and so on, and from B1, D1, E1, etc are the machine names which exactly match the names of each sheet.


So in the master sheet, in B1 is simply MC1 and cell A2 is Week 1; therefore B2 is where we want to know if the machine has had calibration data entered.

In B2, enter the following formula

=IF(VLOOKUP($A2,INDIRECT("'"&B$1&"'!A:O"),15,FALSE)="","No","Yes")

This looks at the week num in the Master, finds the relevant sheet based on the machine name, looks up the week and looks for data entered in Z DV (Column O) against that week.


Copy this formulae Down and then across, then do a conditional formula where "Yes"=Green and "No" = Red

You will have a lot of Red No's unless you have a date on your spreadsheet somewhere and build =today() into your formula.

If this doesn't make sense to you, feel free to send me the file and I'll sort it.
littleclubber@gmail.com

Thank you for the response.

I appreciate the complexity of explaining it, i was struggling to explain the problem myself. I have followed through what you said and made some adaptions to match my spreadsheet but i seem to have gotten it working.

Other than altering the cell locations and rearranging some things in the workbook this has worked perfectly.

Many Thanks for your help
Kind Regards
Kris

 

[Little_Clubber]

Glad I could help.