Cell dependent selection

[RID]

I am trying to automate a quality control sheet, and when I enter information in Cell A9 which could be made up of numbers & letters, it makes cells C9 to V9 mandatory, then if a second item no is put into A10 then it only picks certain tests from C9 to V9 not them all. Ideally when the document is then put to save it would alert the user that cells are missing data.

Is this possible to do with just formulas or would it require VBA code also.

Any pointers would be appreciated

 

[RID]

I have managed to do what I wanted to do using conditional format settings using the AND formula. The formula I have used is =AND($A9<>"",C9=""), this is allowing me to enter the ID into cell A9 and then it highlights the cell C9 as a must have test using conditional formatting, when the cells is then have an entry into it, it turns back to white. Copied all the formats to each cells applicable to.


However I am now stuck on the last piece of the jigsaw.

Problem 1 - Currently I have the formula for conditional formatting in H10 to H13 based on the formula =$H$9<>"", where when text is input into H9 (MrExcel2.png) then cells H10 to H13 are greyed out. However if the test was conducted in H10 (MrExcel 3.png) instead of H9 which formula should I use for greying out H9, H11, H12 & H13. Would this formula work if it was entered into any of the H9 to H13 and grey out the remaining 4.

Problem 2 - I have another column with exactly the same issue as problem 1 but instead of one test per day this has 2 per day so I am looking for a formula which would allow to pick 2 from a column of 5 but grey out the other 3 cells when 2 has been entered. Is it possible to combine an IF & AND formula combined?