Copy text from multiple sheets to a summary sheet

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
104
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hello peeps.

Is there a formula where I can copy/consolidate text from multiple sheets to a summary sheet? I have a formula that can copy from one other sheet (='Sheet1'!$A1), but can't work out how to do more than one sheet (I've tried ='Sheet1'!$A1, 'Sheet2'!$A1, etc., but it doesn't work).

Any suggestions would be gratefully received!
Sara
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,651
Office Version
  1. 2013
Platform
  1. Windows
Try this:
=CONCATENATE(Sheet1!A1,Sheet2!A1,Sheet3!A1)

 

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
104
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
That's grand, thank you! 😊

And if I wanted to add a comma between the other sheet texts (I tried just adding an extra comma, but it didn't work) and eliminate duplicates? (Realised that I just need 'most attended' and some are duplicated, which I don't need!)
 

Rickinnocal

New Member
Joined
Dec 14, 2010
Messages
33
Office Version
  1. 365
Platform
  1. Windows
To add a comma between the texts, use:

=CONCATENATE(Sheet1!A1,", ",Sheet2!A1,", ",Sheet3!A1)
 

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
104
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Perfect; thank you! And is there a way of removing duplicate text (i.e., if there are 2 x 'Dance', I only want 1 showing)?
 

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
104
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thank you for the link. However, I have 23 different text options from four different sheets going into one summary cell. I think (if I'm understanding it correctly, of course!) that I would have to list out all the 23 text options and the four different sheets with the Concatenate, which kinda blows my mind!

I found a formula online and tried to tweak that for my four sheets, but I'm getting an error that I've entered too many arguments:

=SUBSTITUTE(TRIM(A2&" "&IF(B2<>A2,B2,"")&" "&IF(COUNTIF(A2:B2,C2)=0,C2,"")&" "&IF(COUNTIF(A2:C2,D2)=0,D2,"")), " ", " - ")

I tried:
=SUBSTITUTE(TRIM('Sheet1'!$A1&" "&IF('Sheet2'!$A1<>'Sheet1'!$A1,'Sheet2'!$A1,"")&" "&IF(COUNTIF('Sheet1'!$A1:'Sheet2'!$A1,'Sheet3'!$A1)=0,'Sheet3'!$A1,"")&" "&IF(COUNTIF('Sheet1'!$A1:'Sheet3'!$A1,'Sheet4'!$A1)=0,'Sheet4'!$A1,"")), " ", " , ")
 

Watch MrExcel Video

Forum statistics

Threads
1,132,976
Messages
5,656,184
Members
418,288
Latest member
reba557

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