2

I have some cells which I would like to format to display the level achieved for each person: enter image description here

On the right I have a table called Table3 which contains the data of the level achieved by each person. This is shown on the left by a bar graph.

The formula I have in B2 to give me the TRUE and FALSEs for the conditional formatting is: =VLOOKUP($A2,Table3,2,FALSE)>=B$1. However, if copy and paste this formula into the conditional formatting dialogue box I get an error saying the formula is not valid. If I however replace Table3 with $G$2:$H$5 it works perfectly.

So, why does conditional formatting not like my table names, and is there a way to use tables when doing conditional formatting?

Improve this question
6
  • Do you have any hiding rows or filter in Table3? if it is normal no filter no hidden rows it worked with me
    – yass
    Jul 10, 2017 at 19:43
  • I don't have anything hidden or filtered in by workbook
    – M.Hesse
    Jul 10, 2017 at 19:57
  • I just looked at this again and it appears to be problem with using table names, not named ranges. I have amended by question to specifically ask about table ranges.
    – M.Hesse
    Jul 10, 2017 at 20:04
  • If Table3 is not a named range then you have to write sheet!$G$2:$H$5 in Vlookup you cannot just write the name of the sheet only
    – yass
    Jul 10, 2017 at 20:10
  • use named range instead of sheet name in conditional Formatting
    – yass
    Jul 10, 2017 at 20:30

1 Answer 1

Reset to default
2

To reference a table within conditional formatting formula you will need to use INDIRECT("<Table_Name>").

Your formula will thus be : =VLOOKUP($A2,INDIRECT("Table3"),2,FALSE)>=B$1

I don't know why but it just works.

Reference: How to use a table name in data validation lists and conditional formatting formulas

(BTW: Why don't you just use the "Data Bar" Conditional Formatting in your table? It would be much easier.)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.