Forum Discussion
Conditional Formatting Based on a Table
Hi,
I was wondering if it's possible to do conditional formatting based on the information presented in a table. What I'm specifically looking for is a bit complicated so here's a picture of my sheet.
So based on the picture, I want the grey squares to turn either orange or black based on the what is written underneath. Say, instead of 'goalkeeper' we have 'a'. If the M/F value for 'a' in the table is M, I want the grey to turn red, if the value in the column (for 'a') is F, I want it white. However, if 'b' was written instead of 'a' I want the colour to be based on the M/F value for 'b'. Hopefully that's a good enough explanation of what I'm looking for.
Is this a thing that's possible with excel or am I looking at having to put all the info in the picture into code (html/js/css) and doing it that way? If it is possible, what would I have to do?
Thanks
You can use a combination of formulas and conditional formatting rules to achieve the desired result.
Here is a summary of the steps:
Select the range of cells (the grey squares) that you want to apply the conditional formatting to.
Go to the "Home" tab in the Excel ribbon and click on "Conditional Formatting" in the "Styles" group.
From the dropdown menu, select "New Rule" to open the "New Formatting Rule" dialog box.
In the dialog box, select the option "Use a formula to determine which cells to format".
In the "Format values where this formula is true" field, enter the following formula:
=IF(INDEX(Table1,MATCH($A$1,Table1[Code],0),MATCH($B2,Table1[#Headers],0))="M",TRUE,FALSE)
Note: Adjust the formula according to your specific table range and cell references.
Click on the "Format" button and choose the desired formatting style for the cells (e.g., red fill color for M, white fill color for F).
Click "OK" to close the dialog box.
Please note that there may be slight differences in the user interface or menu options between Excel versions, but the general concept of conditional formatting based on a formula remains the same. By using this approach, you can dynamically apply the formatting based on the values in the table, without the need for external code or languages like HTML/JS/CSS.
The text and formulas was created with the help of AI.
Hope this will help you.
So, I tried the formula with my details and I keep getting an error message.This is what I've put in
=IF(INDEX(Table110,MATCH($AF$3,Table110[Code],0),MATCH($AH4,Table110[#Headers],0))="M",TRUE,FALSE)
I have also tried it with [Code] b4eing my range of cells for the table and it still doesn't work. Have tried a number of changes to the $AF3/$AH$4 parts none of which are working.
Any idea what I've done wrong?
Thanks
The error you are encountering could be due to a couple of possible reasons:
- Make sure that the table range reference (Table110) matches the actual table range in your worksheet. Double-check that the table name and range are correct.
- Verify that the cell references ($AF$3 and $AH4) correspond to the correct cell locations in your worksheet. Ensure that the referenced cells contain the expected values for matching against the table.
Here are a few troubleshooting steps you can follow:
- Check the table range: Select the table in your worksheet, go to the "Table Design" contextual tab in the Excel ribbon, and verify the table name and range. Make sure that the table range matches the one referenced in the formula (Table110).
- Validate the cell references: Double-check the cell references $AF$3 and $AH4 to ensure they are pointing to the correct cells. Confirm that these cells contain the values you expect for matching against the table.
- Evaluate the formula: You can use the "Evaluate Formula" feature in Excel to step through the formula and see how the calculations are performed. This can help identify any potential errors or mismatches. To use this feature, select the formula cell, go to the "Formulas" tab in the Excel ribbon, and click on "Evaluate Formula."
- Test with simplified formulas: As a troubleshooting step, you can try simplifying the formula to focus on one specific part. For example, you can remove the INDEX and MATCH functions temporarily and test the formula with a simple comparison (e.g., =$AF$3="M"). This will help isolate any issues with the table lookup.
By following these steps and double-checking your table range and cell references, you should be able to identify and resolve any errors in the formula. If not works following these steps, please inform about your Excel Version, Operating system, storage medium, etc.
The steps were processed using AI.
- Amazing, I'll give it a go! Thank you
