SOLVED

Populating a finished document using tick boxes

Copper Contributor

Hi I'm currently trying to create a document which allows people in the office to create a finished H&S document with existing text inputted using tickboxes.  I've installed the tickboxes and the relevant info associated with them without any issues however the only way I could think of populating the final document was using the filter function 

 

=FILTER('Workings page'!E13:E18,'Workings page'!D13:D18=TRUE,0)

 

This works fine on the simple lists where the data all fits on one line, however some of the data I wish to input is pretty much a paragraph of text and therefore it would spread over a number of lines.

 

When I tried merging the cells to fit the data within one cell it didn't work due to the spill error.  

 

I'm not an expert in Excel so I was wondering whether there would be a method to insert the relevant text without a one line limit?

For example I was wondering whether a macro could be used to insert some rows if the tick box was checked?  Or whether there is a different function that would lend itself to the task that wouldn't raise the spill error?

4 Replies
Why not enter that text into a single cell? You can make the column wider and the row taller?
best response confirmed by whall316 (Copper Contributor)
Solution

@whall316  I don't know if I understand the problem.  Are you looking for automatic word wrapping?  Here is the button on the Home Tab:

mtarler_0-1663855409306.png

it is the button with the ab and the c below (i.e. wrapped to next line)

if you have spill error because more than 1 item is checked then add a Concatenate or TextJoin function around the FILTER:

=TEXTJOIN(" ",TRUE,FILTER('Workings page'!E13:E18,'Workings page'!D13:D18=TRUE,0))

@Jan Karel Pieterse the only issue with this is that the finished document is already pretty much complete with all the column widths etc already set.

Hi, thanks for this - the textjoin work around will work fine once I rearrange the layout to suit.

Thanks for the input.
1 best response

Accepted Solutions
best response confirmed by whall316 (Copper Contributor)
Solution

@whall316  I don't know if I understand the problem.  Are you looking for automatic word wrapping?  Here is the button on the Home Tab:

mtarler_0-1663855409306.png

it is the button with the ab and the c below (i.e. wrapped to next line)

if you have spill error because more than 1 item is checked then add a Concatenate or TextJoin function around the FILTER:

=TEXTJOIN(" ",TRUE,FILTER('Workings page'!E13:E18,'Workings page'!D13:D18=TRUE,0))

View solution in original post