How to Return into Single Cell the Text from Multiple Columns Same Row.

[blakeb]

Hello,
This is my first post so please excuse me if I do something wrong.
I have a workbook that contains approximately 260 sheets, uniquely named.
Each sheet contains a row, say row 22 that is labeled in column A "Review Comments:".
Column C through Column Z "MAY" contain text comments i.e., some columns blank, some contain text.
I have an INDEX sheet that contains each of the worksheet names in column A.
In Column B, I want to return the text from Row 22, columns C through Z of each sheet. The name of each sheet is in column A of the INDEX sheet.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]MULTIPLE SHEETS[/TD]
[TD]REGION_1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[/TR]
[TR]
[TD]Hours[/TD]
[TD]1500[/TD]
[TD]1625[/TD]
[TD]800[/TD]
[/TR]
[TR]
[TD]Headcount[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Review Comments:[/TD]
[TD]02/08/2018 Contact Provider to find out why no Headcount entered.[/TD]
[TD][/TD]
[TD]04/09/2018 Check why drop in Hours[/TD]
[/TR]
</tbody>[/TABLE]














[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]INDEX WORKSHEET[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]REGION_1[/TD]
[TD]02/08/2018 Contact Provider to find out why no Headcount entered. 04/09/2015 Check why a drop in Hours[/TD]
[/TR]
[TR]
[TD]REGION_2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]










Right now on the INDEX sheet, I am using a formula in columns C through Z: (=IF(INDIRECT("'"&$A3&"'!"&C$1&"22")=0,"",INDIRECT("'"&$A3&"'!"&C$1&"22"))
to return the data from each sheet.

$A3 = Region Name on INDEX sheet
C$1 = Region Name on Region worksheet
22 = Row number where Review Comments if found.
If no comments found, change the 0 that is returned, into a blank.

Then in column B, I am using a formula to concatenate the columns:
=SUBSTITUTE(CONCATENATE(C4," ",D4," ",E4," ",F4," ",G4," ",H4," ",I4," ",J4," ",K4," ",L4," ",M4," ",N4," ",O4," ",P4," ",Q4," ",R4," ",S4," ",T4," ",U4," ",V4," ",W4," ",X4," ",Y4," ",Z4," ",AA4),"0","")

In column B, Concatenate all the comments that are returned for the specified Region adding a space in between each one.

Is there a better way to do this?