Hello,
I need some help using conditional formatting. I have a spreadsheet with employee payroll data. In column A I have a formula that numbers the row. I did this, so that when I filter the data based on salary amount over a $ threshold, I could have a numbered list. The formula that I have in that column is =SUBTOTAL(2,G$2:G2). In column H I have dollar amount for each employee. In column P, I used a formula to create a random number. The formula that I have in column P is =RAND(). In column I, I have another formula that tells me what sample to select in my audit of the payroll data. For example, if cell I2 reads 228, I will select the employee that is 228 in column A. The formula that I have in column I is =IF(ROW()-ROW($I$2)+1>$J$1*COUNTA($A$2:$A$839),"",INDEX($A$2:$A$839,RANK(P2,$P$2:$P$839))). Cell J1 reads 10%.
So say that I wanted to see all the employees earning over $2,000 in this pay period, I filter on column H for all the values greater or equal to $2,000. Once I filter the date, column A and column I automatically update. In column I only shows values based on 10% of the number of employees that earn over $2,000. I would like to create a conditional format that will highlight the cell in column A that equals to the number in column I.
I hope this makes sense.
Any help is appreciated.
I need some help using conditional formatting. I have a spreadsheet with employee payroll data. In column A I have a formula that numbers the row. I did this, so that when I filter the data based on salary amount over a $ threshold, I could have a numbered list. The formula that I have in that column is =SUBTOTAL(2,G$2:G2). In column H I have dollar amount for each employee. In column P, I used a formula to create a random number. The formula that I have in column P is =RAND(). In column I, I have another formula that tells me what sample to select in my audit of the payroll data. For example, if cell I2 reads 228, I will select the employee that is 228 in column A. The formula that I have in column I is =IF(ROW()-ROW($I$2)+1>$J$1*COUNTA($A$2:$A$839),"",INDEX($A$2:$A$839,RANK(P2,$P$2:$P$839))). Cell J1 reads 10%.
So say that I wanted to see all the employees earning over $2,000 in this pay period, I filter on column H for all the values greater or equal to $2,000. Once I filter the date, column A and column I automatically update. In column I only shows values based on 10% of the number of employees that earn over $2,000. I would like to create a conditional format that will highlight the cell in column A that equals to the number in column I.
I hope this makes sense.
Any help is appreciated.
