Good morning,
So I have this spreadsheet with 630+ records, spanning 1930+ rows. Yes, there are lots of merged cells in about half the records. Based on the text ("H" / "M" / "N/A") in a particular cell of each record, telling applicability of each record to the final baseline set when completed, I want to highlight records that have N/A as not applicable.
<background> The spreadsheet is a template for customers to set a baseline of items, dependent upon their particular system configuration and environment. We are having them justify anything that is not a value of H. If the value of the item is M (for medium confidence/applicability), they have to decide of it is applicable, and justify it (in a comment cell) as part of the new baseline. If the value is N/A, they have to provide justification as to why the item is not part of their baseline. Pretty simply stuff. </background>
I was able to conditionally format the confidence/applicability column, to change the cell to green for a value of H, and yellow for a value of M, and orange for a value of N/A. I want to further highlight the N/A's to make the whole record orange, to have the visual cue that a particular record has been excluded. The only way I've found to accomplish this is to apply the formula {=$G$5:$G$65536="N/A"} as applicable to record 1 {$G$5:$G$38}, and so on for each record. Is there a way I can do this without having to spend the time to set the applicability for each record (because of the merged cells - some have one line, some have a few, and some have many lines), or am I screwed because of the merged cells?
I went through the arduous process of applying the above formula to each record a few days ago, figuring I would only have to do it once, and then I found a mistake when we exercised the spreadsheet as if we were a customer (BETA testing, as it were). I absolutely got stuck (made way too much brain smoke yesterday) in trying to figure out my mistake, and decided to start over, clear the formatting on the rows, and ask you fine folks for assistance. Please help.
Any insight would be greatly appreciated.
So I have this spreadsheet with 630+ records, spanning 1930+ rows. Yes, there are lots of merged cells in about half the records. Based on the text ("H" / "M" / "N/A") in a particular cell of each record, telling applicability of each record to the final baseline set when completed, I want to highlight records that have N/A as not applicable.
<background> The spreadsheet is a template for customers to set a baseline of items, dependent upon their particular system configuration and environment. We are having them justify anything that is not a value of H. If the value of the item is M (for medium confidence/applicability), they have to decide of it is applicable, and justify it (in a comment cell) as part of the new baseline. If the value is N/A, they have to provide justification as to why the item is not part of their baseline. Pretty simply stuff. </background>
I was able to conditionally format the confidence/applicability column, to change the cell to green for a value of H, and yellow for a value of M, and orange for a value of N/A. I want to further highlight the N/A's to make the whole record orange, to have the visual cue that a particular record has been excluded. The only way I've found to accomplish this is to apply the formula {=$G$5:$G$65536="N/A"} as applicable to record 1 {$G$5:$G$38}, and so on for each record. Is there a way I can do this without having to spend the time to set the applicability for each record (because of the merged cells - some have one line, some have a few, and some have many lines), or am I screwed because of the merged cells?
I went through the arduous process of applying the above formula to each record a few days ago, figuring I would only have to do it once, and then I found a mistake when we exercised the spreadsheet as if we were a customer (BETA testing, as it were). I absolutely got stuck (made way too much brain smoke yesterday) in trying to figure out my mistake, and decided to start over, clear the formatting on the rows, and ask you fine folks for assistance. Please help.
Any insight would be greatly appreciated.
