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

blakeb

New Member
Joined
May 9, 2018
Messages
1
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.
MULTIPLE SHEETSREGION_1
JanFebMar
Hours15001625800
Headcount1110
Review Comments:02/08/2018 Contact Provider to find out why no Headcount entered.04/09/2018 Check why drop in Hours

<tbody>
</tbody>














INDEX WORKSHEET
REGION_102/08/2018 Contact Provider to find out why no Headcount entered. 04/09/2015 Check why a drop in Hours
REGION_2

<tbody>
</tbody>










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?
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Watch MrExcel Video

Forum statistics

Threads
1,122,160
Messages
5,594,597
Members
413,916
Latest member
Islandlady

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top