255 Excel Character Limit in Formulas

[Joe Ross]

Seeking help on solving an Excel issue, please:

I use Text Boxes in the spreadsheets I create for meta crosswords.

I recently discovered that the text boxes' content can be inserted via formulas, which has reduced prep time & improved accuracy for inserting clue numbers in the grids.

With the WSJCCs moving to elongated, legal paper-ratioed PDFs & with "clue inflation" making reading PDF clues much more difficult within a spreadsheet, I first added 2-columned text boxes to list the clues in a larger font, which also had the benefit of being able to copy & paste the clues from the text boxes to the notes & "list" areas of spreadsheets. In the last month or two I've eliminated the PDF images altogether, since - in my personal experience - the clues within the text boxes are much better to use. Losing the PDFs also decluttered the spreadsheets.

The spreadsheets offer 15 grids running vertically down the left side of the screen. I offer 8 text boxes of clues on the right side of the screen, at every other grid, beginning with the top grid. Currently, I create the text box of clues at the first grid, then copy, paste, & reposition them below.

HERE'S MY ISSUE: To simplify individual meta spreadsheet creation (i.e. no copying & pasting), I've tried using formulas to insert the clues' text from merged cells on other sheets within workbooks. However, I've discovered that there is a 255-character limitation for formulas within Excel, which is roughly equal to 1/10th of the clues being shown within the text boxes. I've started interwebbing this & discovered that there are ways to get around the limit, but - before going blind & craz(ier) - it seems most workarounds are more convoluted than continuing with my current routine.

If you can help, I thank you in advance for PMing me. It may be easier to Skype or Zoom to share screens &/or to work on the spreadsheets co-currently so that I can better describe my specific issues, then for you to learn me some tech with your solution(s).

NOTE: Beyond saving me time in creating spreadsheets for individual metas, I am simplifying the logical process, too, so that meta creators can create them for themselves, if they wish or if I am not able. I have had HUGE help from @KayW since the beginning of spreadsheets & have been offered appreciated expert editing by @Meg for instructions which will be included with meta templates. I'm hopeful there are muggles who can point me in the right direction and tell me to quit whining & get'er done!

[MMe]

Can you give a clean example of a cell that references, merges, and limits as you describe? Maybe attach a small spreadsheet that isolates the issue?

[Joe Ross]

Can you give a clean example of a cell that references, merges, and limits as you describe? Maybe attach a small spreadsheet that isolates the issue?

I think I've hacked it using THIS ADVICE.

This type of text box doesn't allow for columns (I'd like to have 2 columns), but I can group 2 text boxes for the same effect.

EDIT: SUCCESS!

Better:

• This ActiveX type of Text Box doesn't allow for columns, but it does allow for scrolling.

• The clues can be highlighted, copied, & pasted to notes area, or wherever a solver wishes.