Hi everybody,
I've searched around on the internet to find an answer to this question, but I have not found one. The gist is as follows:
I have data on one worksheet, Sheet1, that is sorted by name (rows) and date (columns). I have other data in a second worksheet, Sheet2, that is sorted by name and date, but the order of the names and the dates is different. I want the fill in sheet 2 corresponding to a particular name and date to be conditionally formatted based on the value in Sheet1 corresponding to the same name and date. For instance, in Sheet1, cell B1 which corresponds to the name "1" and the date 1/1/2018 is given a number between 0 and 1. In Sheet2, I used the following formula:
to format the cell in Sheet2 with the same name and date depending on whether the number in cell B1 was greater than 0.5. Though the formatting was not applied, when I pasted the formula into the cell itself it returned the correct true/false value. Why would this be? I uploaded a sample file below. Thanks for your help!
Note 1: The sample file is not the worksheet that I am working on, so reordering the data would not be an option in my case.
Note 2: I posted this question on StackExchange last week, but I have not received an answer. If I do, I will share it here.
I've searched around on the internet to find an answer to this question, but I have not found one. The gist is as follows:
I have data on one worksheet, Sheet1, that is sorted by name (rows) and date (columns). I have other data in a second worksheet, Sheet2, that is sorted by name and date, but the order of the names and the dates is different. I want the fill in sheet 2 corresponding to a particular name and date to be conditionally formatted based on the value in Sheet1 corresponding to the same name and date. For instance, in Sheet1, cell B1 which corresponds to the name "1" and the date 1/1/2018 is given a number between 0 and 1. In Sheet2, I used the following formula:
Code:
=INDEX(Sheet1!$B$2:$K$11,MATCH($A2,Sheet1!$A$2:$A$11,0),MATCH(B$1,Sheet1!$B$1:$K$1,0))>0.5Note 1: The sample file is not the worksheet that I am working on, so reordering the data would not be an option in my case.
Note 2: I posted this question on StackExchange last week, but I have not received an answer. If I do, I will share it here.