Nov 26 2021 03:56 AM - edited Nov 26 2021 04:18 AM
I am using an IF statement to copy data from another cell, but I cannot get the conditional formatting to work on the cell containing the formula? I think this is because the conditional formatting is picking up the formula instead of actual value it displaying. Is there a way to automatically get get the formula to paste or copy this value as a true string so that the conditional formatting works ?
Nov 26 2021 04:07 AM
Conditional formatting acts on a cell's value, not on its formula.
Could you attach a small sample workbook demonstrating the problem, and explain what the conditional formatting rule should do?
Nov 26 2021 04:17 AM - edited Nov 26 2021 04:21 AM
hi Hans
the conditional formatting is between two cell values to determine if the entered results are within limit or not, any rows marked x are to be manually entered results, this works fine. but it is when the data is copied on the rows that are not marked that doesn't work with conditional formatting.
Nov 26 2021 04:55 AM
Thanks! The problem is that the values in B16:B32 are text, so the mess up the numerical comparison when used in the formulas in M16:P32.
You can correct this as follows:
Nov 29 2021 01:46 AM - edited Nov 29 2021 03:02 AM
the report has data imported from another software so row B automatically turns back into text format, when changing text to columns it removes the decimal places in column B, the decimal places need to remain the same. its very close to what I am after ! is there any other ways to do this so it automatic and the decimal places are not changed?
I have attached the blank template and the exported results prior to what you have said below. it doesn't matter that not all of the values exported are not be in-between column G and H. if you could take another look at this it would be much appreciated.
note - it is the QA tap in reference to our subject / topic
Nov 29 2021 04:42 AM
I'm totally confused now.
In your first sample workbook, column B contained single values.
In your "exported data" workbook, however, column B contains a mixture of text values, single values that look like numbers, and two numbers separated by /.
So a "between" comparison makes no sense
Nov 29 2021 05:19 AM - edited Nov 29 2021 05:26 AM
sorry, I should have explained it better ! the idea is to save time on manual entry of results, i want to have column B to be automatically filled in the results area. it will only be the single values this will work on. it doesn't matter if the results copied has text or two numbers separated with "/" as these will be manually typed over, as it isn't correct to the in-between value like you said. so please ignore these. it is only the single values EG rows: 24 - 26 I am interested in. I am not sure if i need a value function somewhere ?
Nov 29 2021 06:41 AM
SolutionThanks for the clarification. See the attached version.
You had rules for most individual rows; I have reduced it to 4 rules for the entire range.
Nov 29 2021 07:20 AM
Nov 29 2021 06:41 AM
SolutionThanks for the clarification. See the attached version.
You had rules for most individual rows; I have reduced it to 4 rules for the entire range.