Conditional Formatting / Mirror text

I have developed an excel spreadsheet to record detail of software testing.

I have applied conditional formatting to sheets 1,2 & 3 which will highlight rows 12-30 based on the input to column G. If "N" is input then the row turns amber and if "Y" is input then the row turns green. This works very well right up until I expand the range by adding more rows in. The "Applies to" field in the "conditional formatting rules manager" behaves in very strange ways and the formatting becomes really unpredictable, meaning I have to constantly go back into the rules manager to correct the range. Is there a way to get the range in the "applies to" field to automatically update, correctly?

The other thing I would like to be able to do would be to get the text from the entire row to mirror into sheet 4 if "N" is in input into column G on sheets 1, 2 or 3. Is there a formula which would allow this to happen?

(I think..) I have attached the spreadsheet I am using in the hope this is helpful.

Thanks in advance!

Hi

I cannot get the spreadsheet to fault.
Is the reference number on each sheet unique.

Cheers

Thanks for your reply.. I have attached an updated version which shows a fault on cell D124. Looking in the rules manager now shows a whole load of rules which I didn't input. Cell D124 is an error which I have no idea of the cause and I do not understand why so many new rules have been added in. This has happened a couple of times now and as I progress the formatting becomes more error-prone in more unpredictable and unusual ways.

The reference number can be unique for sure. That is just for me to be able to link separate testing steps to help guide anyone reviewing the script (assuming you are talking about my inputs in column b when you ask about reference numbers and not some excel terminology I'm unaware of!). I could easily make them unique to each sheet if that would be helpful?

Thanks again

Hi

The error in cell D124 is solely because the conditional formatting had been removed from the cell. Selecting a cell of a similar size and copying the format to cell D124 solved the problem.

I asked whether the reference number was unique so that effort could be devoted to using this as a control to feed data through to other sheets.

I have noticed that the first tab is now named and sheets 2 and 3 are missing. A number of the rules can possibly be deleted when tidying up the spreadsheet.

It would however appear that someone else is building this spreadsheet. This issue needs to be addressed before myself or any other volunteer can provide a solution for you. We need a complete spreadsheet and not one that is changing especially when the areas being changed are the areas requiring assistance.

Can you please confirm the spreadsheet is stable and whether the reference numbers are permanent and intend to be unique.

Thank you

Hi,

I gathered that the conditional formatting had been removed but I can't understand what caused this to happen to this specific cell? This happens to random cells / rows as I input more data into the spreadsheet.

The concept of this is for it to be a blank template that can be used for testing different software - my original attachment is the base template I use for this every time. The 2nd attachment was just an extract purely to try and provide an example of where I had an issue with the conditional formatting (if it removes confusion, this attachment can be ignored for now though and I will try to provide a better example in a future reply). This template will be used slightly differently each time (i.e. sheet 1 only will be needed on some occasions, sheets 1, 2 and 3 on others. Some tests will have a few rows on each sheet - some will have lots of rows on each sheet). Ideally sheets will be renamed to suit the software that is being tested. Each completed spreadsheet will look different, based on the requirements of the software that is being tested but if possible the template should work in the same way for each script.

I'd like to share the template with less tech savvy colleagues who can then start to easily populate test scripts. I can fix the conditional formatting quite easily so this isn't an overwhelming issue for me - I'd just like to try and get confidence that the conditional formatting will consistently work as intended once this is shared. I'm certain that colleagues will become quickly frustrated if it doesn't work (the standard scripts are built in Word and they are not effective - however I'm relatively new in role and the wider team are very established in their ways. If I suggest this change I want to be certain it is as effective as I can make it!)

In terms of the reference number - I am open to any kind of idea regarding the reference number that will help me achieve a successful mirroring of text into sheet 4. At the moment I am creating reference numbers that seem to be logical or pertain to the software / function that is being tested (i.e. if I am testing the "settings" area of a piece of software then I will reference it as "S-1, S-2, S-3 etc.). This is not currently definitely unique to each row on each sheet. I could easily have a standard referencing convention that I could apply to any script (i.e. reference rows in sheet 1 as "1-1, 1-2, 1-3" etc and sheet 2 as "2-1, 2-2, 2-3") if this would be useful?

Thanks!

Hi

Any unique reference number of your choice should suffice.

I have deleted all bar 2 conditional format rules and modified the "Applies to" to include the entire range B13:K200 (the 200 being beyond the existing row size.) Inserting rows seems to work with the formatting carrying through. If the spreadsheet looks like extending beyond 200 rows just change the "Applies to". Set it up for each sheet.

Since you have filtered each of the ranges on Sheets 1,2,3 it may be easy for you as co-ordinator the filter on N and copy the result to Sheet 4 for each of Sheet 1,2,3 then sort Sheet 4 to tidy up. An alternative may be to use a password controlled macro to do this.

Which would you prefer. If you could upload a model with 3 or 4 lines of data on Sheets 1,2,3 a solution can be built fairly easily.

Cheers