I have data across B9:D58
Column B contains name strings (no empty cells)This text will be hidden
Column C contains integers (1 - 36) or an empty cell
Column D contains either “y”, “no” or an empty cell
Elsewhere in the sheet, let’s say B2, I want this cell to display “1” in font size 40, unless a “1” exists somewhere in C9:C58. If there is “1” in that range, I’d like B2 to display the matched number (e.g. “1” this case), followed by a “.” and the contents of the adjacent cell in B9:B58.
I’d also like the cell to have its background colour changed to red if the adjacent cell in D9:D58 is “n”, and green if “y”, but remain white if there is no matched number in C9:C58
I’ve been trying this with MATCH, LOOKUP, and VLOOKUP, but I can’t seem to get enough functionality from just one of these functions, so I wonder whether something else exists to achieve this, or whether I need to create a script/function for this case. If so, how would I do this?
FM layout template 2023.ods (18.4 KB)
Many thanks