Return all Sheet names containing a specified value

[wiggy_worm]

Hi,

Im trying to automate a table so that it adjusts column headers based on a drop-down cell at the top of the worksheet (in cell B1, defined as 'NAME'). The column headers need to adjust to only show the Sheet names that contain 'NAME' within cells E9:AZ9 in each sheet.
All sheets are listed in a named range 'SCHEMES'.

I can return either the first or the last Sheet name, but cant seem to get an array formula to return all of the possible matches.

Formula that returns the first match:

{=IF(ISERROR(INDEX(SCHEMES,MATCH(1,--(COUNTIF(INDIRECT("'"&SCHEMES&"'!$E$9:$AZ$9"),NAME)>0),0))),0,(INDEX(SCHEMES,MATCH(1,--(COUNTIF(INDIRECT("'"&SCHEMES&"'!$E$9:$AZ$9"),NAME)>0),0))))}

Can anybody help with an array formula that will return all matches found?

I then have a second question;
In a separate sheet I have a similar table that adjusts row/column headings. The data lookup then needs to search for a specific column corresponding to the row value across all sheets, and sum the results. The column location varies across the sheets, so I've set it up using the following formula that was an attempt to fudge an INDIRECT function by working out the column letter. While it works fine for up to column 'Z', as soon as the value goes above this the CHAR function doesn't work (starts returning symbols rather than jumping to 'AA', 'AB' etc).

Any ideas on how to fix?!

{=IF(ISERROR((SUMIFS(INDIRECT("'"&C$7&"'!"&(CHAR(68+MATCH($A8,INDIRECT("'"&C$7&"'!E9:AZ9"),0)))&"31:"&(CHAR(68+MATCH($A8,INDIRECT("'"&C$7&"'!E9:AZ9"),0)))&"136"),INDIRECT("'"&C$7&"'!D31:D136"),">="&$C$2,INDIRECT("'"&C$7&"'!D31:D136"),"<="&$C$3))/$B8),"",IF($C$4="Total Hours",((SUMIFS(INDIRECT("'"&C$7&"'!"&(CHAR(68+MATCH($A8,INDIRECT("'"&C$7&"'!E9:AZ9"),0)))&"31:"&(CHAR(68+MATCH($A8,INDIRECT("'"&C$7&"'!E9:AZ9"),0)))&"136"),INDIRECT("'"&C$7&"'!D31:D136"),">="&$C$2,INDIRECT("'"&C$7&"'!D31:D136"),"<="&$C$3))/$B8),((SUMIFS(INDIRECT("'"&C$7&"'!"&(CHAR(68+MATCH($A8,INDIRECT("'"&C$7&"'!E9:AZ9"),0)))&"31:"&(CHAR(68+MATCH($A8,INDIRECT("'"&C$7&"'!E9:AZ9"),0)))&"136"),INDIRECT("'"&C$7&"'!D31:D136"),">="&$C$2,INDIRECT("'"&C$7&"'!D31:D136"),"<="&$C$3))/($B8*((C3-C2)/7)))))}