learning some google sheet formularizing; scout emails and software release versions by site

Matching installed sites and versions and showing selected sites not on latest version.

Taking an item from another sheet . . (a latest published release string)
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/YADDAH", "Summary!C1")

Other sheet "ProdLineSites" created to pull in data on siteids, type, installed version
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/YADDAH", "Sites!B:H")

List ALL SITES siteids in column C and by type (column B)   B2 is the site type in heading column
Dev QA S1 S2 L
=FILTER('Form Responses 1'!$C2:$C1000,'Form Responses 1'!$B2:$B1000 = B2)

List all sites selected by 1 in G column: find siteids(C), by type(B), H2  is the site type in heading column
Dev QA S1 S2 L
=FILTER('Form Responses 1'!$C2:$C1000,'Form Responses 1'!$B2:$B1000 = H2,'Form Responses 1'!$G2:$G1000 = 1)

A single column latest release from sites
=SORT(UNIQUE(ProdLineSites!D2:D2000),1,false)

Selected LAST SUCCESSFUL VERSION, get versions of sites from the production line version .. formula copied .. H3 etc (H3:L1000) refers to the selected siteids. vlookup matches the siteid(B:) and pulls out the version(D:==+3)
Dev QA S1 S2 L
=IFERROR(VLOOKUP(H3,ProdLineSites!$B:$D,3,false),"")

To show errors better if site has no version . .  Which Happened!

=IF(not(J3=""),VLOOKUP(J3,ProdLineSites!$B:$D,3,false),"")

And conditional formating colour . . .
If version doesn't match the latest highlight it as red . . .
To make rule based on reference to cell values use "Custom formula is" . . .
=and(not($M$3=N3),not(N3=""))

Organising contacts . . . making comma-separated email lists . . . by leader type

A sheet per scout group. Row 4 has headings:

Group name

Name

Section

email address

Training Level

Skills have

Skills would like

Email list sheet A1 can be changed to the leader type wanted.

A1: Scout

A2: ^^^ Change me ^^^

$B1 has group names  (i.e. B1 C1 D1 E1 etc)   and J1 has email lists

B3:IN has . . . INDIRECT uses $C6 as sheet name (scout group name) 

address pulls data from the other sheet relative to the current sheet. 

find matching section in $A$1 e.g. "Scout" to Section column C in sheet B$1 (sheet match group name)

if found pull in the email address entry in column D

iferror or not found return empty string

=iferror(if(find($A$1,INDIRECT(address(row($C6),column($C6),4,true,B$1))),INDIRECT(address(row($D6),column($D6),4,true,B$1)),""),"")

B2:I2 has . . .  =iferror(join(",",filter(B3:B60, NOT(B3:B60 = "") )),"")

that joins all the email addresses in column together with "," 

filter to skip blanks 

iferror fill in with blank if no match

J2 combines all email lists from B2:I2 together, filter to eliminate blanks

=iferror(join(",",filter(B2:I2, NOT(B2:I2 = "") )),"")