Creating List to Copy and ignore formula blanks

Hi all

See attachment Example 1

In Cell I8 (merged Cell) im trying to populate a list that can be copied and pasted - ideally a list with line breaks for formatting reasons,

the sheet works that when someone tickets the relevant boxes in Column D it will populate the text in I8 - i have created the checks in columns U-W, W has the check formula if the box is ticked and returns the relevant value we would add, but this also gives blanks to be ignored, but the blanks are based on "" - i would also at the end like to add the "Notes" if entered in C18

I can use forumla's to enter into the box, but it creates terrible formatting - any idea's?

Merged cell was for visibility

but i've attached a "work around" that allows it to show how i'd like and populate

Hi
to populate I8 with line break =TEXTJOIN(CHAR(10),TRUE,$W$5:$W$15)

or =TEXTJOIN(",",TRUE,$W$5:$W$15) to populate with coma separated.

Originally Posted by Glenn Kennedy

No TEXTJOIN in Excel 2010.

i should add I also use Office 365 =/

Originally Posted by Sadath31

Hi
to populate I8 with line break =TEXTJOIN(CHAR(10),TRUE,$W$5:$W$15)

or =TEXTJOIN(",",TRUE,$W$5:$W$15) to populate with coma separated.

this works well for excel - but formatting disappears on copy and paste - maybe trying to do too much from excel?

Originally Posted by Glenn Kennedy

Then update your profile, which shows Excel 2010.

Done - apologies only switched to 365 a few weeks back, forgot the old profile :D

Originally Posted by Glenn Kennedy

Agree withPepe. However, you have two options.

1. A helper column (which can, of course, be hidden).

2. VBA.

You choose.

I don't mind either, but i try to avoid VBA if at all possible due to people disabling Macro's - so helper column would be preferable?

Originally Posted by Glenn Kennedy

OK. In W5, copied down:
=IF(U5="","",TRIM(W4&IF(U5=TRUE," "&V5&CHAR(10),"")))

and in I8 (enable text wrapping):
=TRIM(LOOKUP(2,1/($W$5:$W$20<>""),$W$5:$W$20))

Thanks - I think i can get this to work in a formatting stand point!