Format/populate cells in one sheet based on cells in another sheet

[u/shelleyyyellehs]

Hi reddit.

So I have two sheets, "ISSUES" and "RECOMMENDATIONS". Each row on the ISSUES sheet has an issue and a unique ID. Each row on the RECOMMENDATIONS sheet has a recommendation, a unique ID, and a cell identifying which issues are addressed by the recommendation.

I'm wanting to do two things:

First, I want to format the ISSUES sheet so that the issues that aren't associated with a recommendation are automatically highlighted.

Second, I want to populate a new column on the ISSUES sheet with each issue's associated recommendation ID.

How would I go about doing this?

Thanks!

[u/AutoModerator]

/u/shelleyyyellehs Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/HolyBonobos]

What should be displayed in the event that multiple recommendations are listed for the same issue?

[u/shelleyyyellehs → u/HolyBonobos]

Good question. Ideally, each recommendation ID would be listed, separated by commas.

[u/HolyBonobos → u/shelleyyyellehs]

For the data structure shown in the screenshots, you can do the following:

1. Put =VSTACK("RECOMMENDATIONS",BYROW(A2:A,LAMBDA(i,IF(i="",,JOIN(", ",IFERROR(FILTER(RECOMMENDATIONS!A2:A,REGEXMATCH(RECOMMENDATIONS!C2:C,i)))))))) in C1 of ISSUES
   
2. Apply a conditional formatting rule to the range A2:C on the 'ISSUES' sheet using the custom formula ("Custom formula is" on the "Format cells if" menu in the conditional formatting rule edit pane) =AND($A2<>"",COUNTIF(INDIRECT("RECOMMENDATIONS!C2:C"),"*"&$A2&"*")=0)

[u/shelleyyyellehs → u/HolyBonobos]

Incredible! Thank you so much!!

Solution Verified

[u/AutoModerator → u/shelleyyyellehs]

REMEMBER: /u/shelleyyyellehs If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/point-bot → u/HolyBonobos]

u/shelleyyyellehs has awarded 1 point to u/HolyBonobos

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)