Conditional formatting - not working when displayed by a formula

I have a table called Gender that uses conditional formatting (please forgive the stereotypical colors for F and M!):

I have a table called Student Extended Data that has a field called Gender that uses the Gender table as a Lookup table. The conditional formatting from the Gender table comes accross perfectly:

Finally, I have a table called Student Master that uses a formula to pull the Gender from the Student Extended Data for each student: [Student Extended Data].filter(thisRow.[Student Number]=[Student Number]).Gender

For some reason, when the gender is displayed by the formula, the original conditional formatting is not displayed:

Is there any way to get the formatting to come accross with the formula? Or do I have to recreate all the conditional formatting again and apply it to the formula field?

1 Like

Hi David,

Is the gender column of type Lookup? The styling should carry through if it’s a lookup type column.

A workaround that can be a little more performant is to simply color the text in the cell itself instead of using conditional formatting. This styling should carry through regardless of where it is used.

If a formula like you posted returns a row value, which it does there, I prefer to leave it as a text column type. When you make it a lookup type, the formula calculates that row value, then the lookup type looks to find the row match, which is extra work. On a big table you can start to notice this lag in calculation time.

1 Like

Hi @BenLee, thanks so much for your reply. I didn’t realize that a field with a formula could be of type Lookup. I thought it was type “Formula”, which I guess doesn’t really exist as a “type”! As soon as I switched them to Lookup, they performed as expected. Yay!

I’ll have to keep an eye on speed. If I find that things are getting slow, I’ll try your workaround.

Thank you!


1 Like

Hey Ben, Can I ask for clarification on this? It might be important for other projects.

So when I change the column type to “Lookup”, what does it mean that the formula “calculates that row value” and looks for a match? Does that mean if my formula fetches a row (or rows) from a table, the column type will go looking for those rows again to fetch the conditional formatting?

Thanks for your insight!