VBA extracting data into a new sheet help

[Radams1989]

Hey folks - first time posting and complete average excel user. Chatgpt cannot help me out here, so hoping a human can! Here's my ask:

Sheet1 (Definitions)
Sheet2 (Final List)

Here's what's in Sheet 1:

Select	Definition	Description	Additional
Yes/No	Beach Ball	playable on a beach
Yes/No	Golf Ball	playable on a golf course or football pitch	Football
Yes/No	Football	playball on a football pitch, beach or golf course	Golf Ball, Beach Ball

If I select Yes in any of Column A, I want to extract the Definition (column B) and Description (Column C) into Sheet2 (Final List).

So I could have a list of 20 definitions and then select 4 of them, and only the 4 would appear in the Final List, making it easy for me to cut and paste.

Here's the tricky part.

Sometimes a Definition (column B) requires other definitions to also be included where it is used. The required definitions are in Column D. So using the example above, if I selected Yes for Golf Ball, then the Definition for Football must also be included in the Final List. Or if I selected Yes for Football, then both Beach Ball and Golf Ball must also be included.

It wouldn't matter if the definition is already selected, but where it isn't selected, it would be good to pull it through to the final list and have it highlighted in yellow or a different font color to show that this is not a selected definition but just one being used because it requires it to be accompanied by the selected definition. If that makes sense.

Example:

Beach Ball and Golf Ball are both Selected as Yes.

The Final List should show:

B2+C2
B3+C3
B4+C4 (Highlighted in Yellow)



Football Selected as Yes (only).

B2+C2(highlighted yellow)
B3+C3(highlighted yellow)
B4+C4



Golf Ball selected as Yes (only).

B3+C3
B4+C4(highlight yellow)


Thanks in advance

 

[Peter_SSs]

Give this a try. First, my 'Definitions' sheet

Radams1989.xlsm
	A	B	C	D
1	Select	Definition	Description	Additional
2	No	Beach Ball	playable on a beach
3	No	Golf Ball	playable on a golf course or football pitch	Football
4	No	Football	playball on a football pitch, beach or golf course	Golf Ball, Beach Ball
Definitions

Cells with Data Validation
Cell	Allow	Criteria
A2:A4	List	Yes,No

'Final List' sheet. Nothing showing below headings as all rows in 'Definitions' are "No"

Radams1989.xlsm
	A	B
1	Definition	Description
2
3
4
5
6
7
8
9
10
Final List

Cell Formulas
Range	Formula
A2	A2	=LET(sel,Definitions!A2:A4="Yes",dd,Definitions!B2:C4,a,Definitions!D2:D4,fdd,FILTER(dd,sel,""),ad,TEXTJOIN(", ",,FILTER(a,sel*(a<>""),"")),IF(ad="",fdd,VSTACK(fdd,FILTER(dd,ISNUMBER(MATCH(Definitions!B2:B4,TEXTSPLIT(ad,,", "),0))))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:B10	Expression	=AND($A2<>"",ROWS(A$2:A2)>COUNTIF(Definitions!$A$2:$A$4,"Yes"))	text	NO

Beach Ball and Golf Ball are both Selected as Yes.

The Final List should show:

B2+C2
B3+C3
B4+C4 (Highlighted in Yellow)

Football Selected as Yes (only).

B2+C2(highlighted yellow)
B3+C3(highlighted yellow)
B4+C4

The way I have set this up the 'original' "Yes" selections will always show first with the 'Additional' (yellow) items following.