Excel challenge - wrapping text from a FILTER function

[Ditto]

This one has me stumped for now. I have a sheet with three columns of data that contain 2 textual answers and a final column that identifies a programme the comment relates too.

 

I have a sheet for each programme.

 

I am using the FILTER function in each sheet to pull the 2 columns of data out, the response to the questions.

 

The issue I am having is allowing the output to word wrap where the response is lengthy. Due to data above the FILTER command, I don't want to make the column wider.

 

Trying to merge cells leads to a #SPILL! error. I liked the idea of having the FILTER command as a function on a shape box, but the function that works in a cell doesn't work on shape/text box. It reports "This formula is missing a range reference or a defined name".

 

Any suggestion how to work around this challenge.

 

I will see if I can get an example Workbook as they always explain the problem better, but the machine I am typing this on Excel 2013 and does not have the FILTER command, and my work laptop has M365 (although I almost exclusively use Desktop app), but I don't use Edugeek on that!

[Steve21]

Its hard to understand which error links to what bit without seeing data

 

But on a straight filter, if it's returning multiple results you could use a textjoin True etc and turn it into a single cell with comma seperated

 

If it's a SPILL error it sounds more like that you're trying to reference a table etc, rather than a single cell and would need to use a range

 

Filters should work with just standard text-wrap option too (it won't auto resize the row but it'll work to that column size etc)

 

If you can throw an example up I can take a look over it

 

Steve

[Ditto]

Here's an attached example.

FilterChallenge.xlsx

Edited July 12, 2024 by Ditto

[mavhc]

I just set column A of ProgrammeB to wrap text and it wrapped it fine

[Ditto]

I'll update the example later, but Q2 values from B5 and below should be in cell F5 and below. I then want the wrapping to occur over the width of col A-D. But I don't want to change the width of Cols A and B. Normally, merge cells would be the mechanism. Apart from the fact I generally dislike mergin, try that and see what happens.

[Ditto]

Original example file updated to better show the challenge.

[Ditto]

As an additional note, I've no idea why it returns a "0" when a response is empty, but adding TEXT(,"") overcomes that issue!

[mavhc]

You mean you want the text to go from column a to d, and then wrap back to column a? That's not going to happen

[Steve21]

Doing it like that you'd do a textjoin first then roll it into the merged cell, or do you specifically "need" it on different lines?

 

 

Steve

[mavhc]

Do TEXTJOIN(CH AR(10), .... if you want newlines

[Ditto]

Doing it like that you'd do a textjoin first then roll it into the merged cell, or do you specifically "need" it on different lines?

 

[ATTACH=CONFIG]71890[/ATTACH]

 

Steve

Pending solution to Cloudflare issue

Edited July 15, 2024 by Ditto

[Ditto]

Trying a screenshot of my post!

FilterChallengeSolution.xlsx

[Steve21]

Formula can't be used within shapes like that, only referencing direct cells. So either you do it like you did, or you just slap a normal wrapped text box over the shape and have it hovering etc

 

Steve

[Ditto]

Formula can't be used within shapes like that, only referencing direct cells. So either you do it like you did, or you just slap a normal wrapped text box over the shape and have it hovering etc

 

Steve

Thanks! I think Excel could point me in that direction with a far clearer message than ""This formula is missing a range reference or a defined name". Although, once you know, of course that message makes a lot more sense! I get's that's the value of experience!!!

[Ditto]

Doing it like that you'd do a textjoin first then roll it into the merged cell, or do you specifically "need" it on different lines?

 

[ATTACH=CONFIG]71890[/ATTACH]

 

Steve

 

Very close - thanks! What I like about TEXTJOIN is it removes the array and therefore #SPILL! issues. It set me thinking about using CHAR(10) rather than "," (Thanks @mavhc, but was already there file:///C:\Users\Graham\AppData\Local\Temp\msohtmlclip1\01\clip_image001.png). As ever with Excel, it's a little quirky - the results only displays as wanted with WordWrap. But, as my goal was to put this in a Shape (TextBoxed), just referencing the cell with the results works very nicely. I'll try to post the solution sheet, but having Cloudflare issues!

[Ditto]

Turns out this solution became very short lived! It's limited to 255 characters - gees Microsoft, that's arcane. Now exploring ActiveX control box hidden away under developer mode, but it will be less visually appealing. Power BI probably a better route, but has the complications and expense of more licensing costs!