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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,649
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,640
Messages
5,654,539
Members
418,139
Latest member
nimesh72

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