Conditional Formatting - I'm Stuck

[AMANDAJELLIOTT]

Hi!

This wonderful forum helped me out a while back with a work project I just could not figure out. I am back again with a similar work project that is throwing me for a loop and was hoping you guys could help me learn where I am going wrong. I'm not understanding something about my formulas, and I want to learn!

I have a workbook that has been created for a department within my company. This workbook has an actions table tab that contains list of events and each action that needs to be taken for each event. The tasks that do not need to be completed have an "X" in the cell, the tasks that do need to be completed have a blank cell. Under the MASTER tab, I have created a drop down list for the user to select an event. Once they have made their selection, I have inserted a conditional formatting expression to fill in each cell that had an "X" on my actions table tab as black. This way, the user only sees what actions they need to complete based on the event they have chosen.

BUT, this is not working and I dont know why. I have looked at this for so long my eyes are crossing . Can someone please help?

The forumla I have inserted in conditional formatting is: =INDEX(actions_Table!C$5:R$29,MATCH($F2,actions_Table!$B$5:$B$29,0))="X"

Thank you so much for your assistance!

 

[6StringJazzer]

When showing screenshots of a worksheet please include the row and column headers. Your formula refers to several columns and I don't know for certain where they are on your screenshots. I'm taking an educated guess.

not working

Please define what this means. What result are you expecting, where, and what result are you actually getting?

The data in sheet actions_Table appears to start in row 2 but your CF formula starts in row 5. That's a killer right there.

Also, your INDEX array is using multiple columns when only one is needed. I am not sure what it will return if you have multiple columns but omit the column argument. Probably the first column, but this should be cleaned up.

=INDEX(actions_Table!C$2:C$29,MATCH($F2,actions_Table!$B$2:$B$29,0))="X"

If that doesn't work then maybe you can paste in some actual data instead of pictures, or use a file-sharing site to share your file.

 

[AMANDAJELLIOTT]

When showing screenshots of a worksheet please include the row and column headers. Your formula refers to several columns and I don't know for certain where they are on your screenshots. I'm taking an educated guess.


Please define what this means. What result are you expecting, where, and what result are you actually getting?

The data in sheet actions_Table appears to start in row 2 but your CF formula starts in row 5. That's a killer right there.

Also, your INDEX array is using multiple columns when only one is needed. I am not sure what it will return if you have multiple columns but omit the column argument. Probably the first column, but this should be cleaned up.

=INDEX(actions_Table!C$2:C$29,MATCH($F2,actions_Table!$B$2:$B$29,0))="X"

If that doesn't work then maybe you can paste in some actual data instead of pictures, or use a file-sharing site to share your file.

I am so sorry! I used a file sharing site and the file can be found here: Gofile - Free file sharing and storage platform

It's not working, as in it is not doing anything at all. Once the event is selected, the cells are not formatting as I want it to. Its simply doing nothing.

 

[6StringJazzer]

You didn't use the formula I gave you.

=INDEX(actions_Table!C$2:C$29,MATCH($F2,actions_Table!$B$2:$B$29,0))="X"

You still had this

=INDEX(actions_Table!C$2:R$29,MATCH($F2,actions_Table!$B$2:$B$29,0))="X"

Activity Workbook - OPEN ENROLLMENT MASTER 2022.xlsx

Shared with Dropbox

www.dropbox.com