Copy text from multiple sheets to a summary sheet

[SaraWitch]

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

 

[My Aswer Is This]

Try this:
=CONCATENATE(Sheet1!A1,Sheet2!A1,Sheet3!A1)

 

[SaraWitch]

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]

To add a comma between the texts, use:

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

 

[SaraWitch]

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)?

 

[Jimmypop]

Try giving the example here a go...

Concatenate, but remove duplicates

 

[SaraWitch]

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,"")), " ", " , ")