-1

I’m wondering if someone can assist with a conditional formatting issue i just can't wrap my head around

We currently have a table with names in and next to the name a number 

Bob 5
Michael 6

Now if i type bob in cell A1 i would like it to highlight that row and the four below it. another example would be if i type Michael in cell A1 it would highlight that row and 5 below that

any help is appreciated

user3306104
  • 25
  • 2
  • 8
  • 'highlight that row and the four below it': do you mean the 4 rows? And when you say 'row' does that really mean the whole row or only one cell? – Jerry Oct 31 '14 at 11:18
  • Hi Jerry - Yes the who row. the problem i have is the information in cell A1 is dynamic and sometimes i need 5 rows highlighted and other time maybe 6 - it all depends what word/value is inputted in A1 – user3306104 Oct 31 '14 at 11:20
  • Ok, one last: so the conditional formatting is taking the number next to the name and using it to highlight rows? So if the number was 10, 9 rows below the name will have to be highlighted? – Jerry Oct 31 '14 at 11:22
  • Thanks for your reply - you are correct that is exactly what im trying to do – user3306104 Oct 31 '14 at 11:25

1 Answers1

0

I couldn't write a simpler formula, but that one worked with the data I tested.

A1 will be where names will be inserted.

The table will range from C1 to D7 (assuming there are no column labels).

Select the table C1 to D7 and insert conditional formatting with formula and use the formula:

=AND(COUNTIF(OFFSET(C1,IF(ROW()-VLOOKUP($A$1,$C$1:$D$7,2,0)<=0,1-ROW(),1-VLOOKUP($A$1,$C$1:$D$7,2,0)),0,VLOOKUP($A$1,$C$1:$D$7,2,0)),$A$1)>0,ROW()>=MATCH($A$1,C:C,0))

And pick the formatting your want.

The formula checks two conditions:

COUNTIF(OFFSET(C1,IF(ROW()-VLOOKUP($A$1,$C$1:$D$7,2,0)<=0,1-ROW(),1-VLOOKUP($A$1,$C$1:$D$7,2,0)),0,VLOOKUP($A$1,$C$1:$D$7,2,0)),$A$1)>0

This checks if there is at least 1 match within a designated range depending of the value in D. It will check if there is a match x rows above the current row where x is the value. If there is, the we get the first boolean value (true or false).

The second ROW()>=MATCH($A$1,C:C,0) ensures that the match is above or on the current row.

Google Spreadsheet Demo

Jerry
  • 70,495
  • 13
  • 100
  • 144