I am using the following formula to collate text from 3 sheets into a 4th.
=$'sheet 1'.A1&CHAR(10)&$'sheet 2'.A1&CHAR(10)&$'sheet 3'.A1&CHAR(10)
this collates the text with a return after each sheets contribution. However the return remains even it the is no text in the corresponding box e.g. sheet 1 has 'hi' sheet 2 empty, sheet 3 there looks like:
leaving a return between the 'hi' and the 'there' where any text from sheet 2 would be. I want the return only to be used if there is text from the corresponding cell so there is no return space between.
Hope that makes sense.