Replies

Another option (which I prefer, hugely, but maybe that’s personal) is to use Excel rather than Word for forms.
If you don’t know how much a particular filler-in is likely to put into a cell, it can be formatted with the ‘shrink to fit’ option.
Try protecting the sheet and unprotecting the cells inviting the responses, then the completer can go through the form using the tab key.
If you put instructions for completing the form in a text box with properties set to ‘not print’, it won’t, er, print.
Wonderful what comments you get in this lounge, isn’t it?

A response from an amateur: this trick can be done, but I can only do it inelegantly.
Plough through all the stages of the Tools | Mail Merge wizard (create document, create or open data source, populate document with merge fields, set query options if any, sort the records if you want to.) Then click the Merge button.
You’re offered the choices, ‘To Printer’ or ‘To separate documents’.
When I did this, I got 1 document with each target getting a separate page (it was only a 1-page document, with only 1 merge field. The helpful filename was ‘Document 1’)
The very inelegant ways of handling this are to save as… the ‘Document1 30 times and rub out the pages you don’t want, or to set the query option 30 times and get a different ‘Document 1’ 30 times.
With only 30 records, this is tedious but achievable: for the better way, I’m watching this space, too!
The frilly add-on would be to zip each document as it’s created, too!
Do you plan to do this very often? If Word has a macro recorder like Excel’s, that would be a no-knowledge-required way of automating it – for subsequent occasions, anyway.

Thanks very much, Hans!
(I didn’t realise you sullied your brain with anything not involving VBA, or at the very least, entities which don’t live in cells!)
Will have a go at it tonight, and come back if I need to….

…but it is wysiwyg is you use the print screen feature instead of printing it properly

Thanks v much, Hans.
Brain now back in gear, helped by your prompt.

Is there no mileage in doing the ‘transpose’ bit, if the data source has fewer than 256 rows (and fewer than 65000 columns!)

ok Roberts Jon,
I’m sure you’ll get some clever stuff for doing exactly this, but I belong to the ‘keep it simple’ tendency. You could consider using the text to columns thing to split the names by fiinding the space, then doing the inversion and pasting special that way.
The macro recorder will faithfully watch and record this as you do it. The only improvement I’d make to the code generated is to add a new first line
Application.screenupdating=false
Suggest then attach it to a button or a toolbar.
One thing I’ve found handy when accepting other people’s lists is the facility by using the =proper function to turm JON ROBERTS into John Roberts, if you wanted to.

….. and if you only want to transmit values, saving as .csv results in significant space saving (especially if you then zip it, too!)

With winzip, you can of course use the ‘make .exe’ option so your target wouldn’t need winzip to open and read it.

Might this ‘feature’ be related to the irritating unreliability (in 97 and 2000 at least) in the matter of what you see in the cells not being what you see in print preview (or in print)?
The attached snippet illustrates this admirably, and it’s a Bad Thing. (Because you send someone a file set up just nice to print ‘correctly’, but in order to read it on screen, they adjust the cell height or width or both, thus screwing up the way it’s intended to print….)

You could try the Tools | Options |View | Window ‘show formaulas’ choice, resize the cells to make ’em all readable, then do the Shift / Edit ‘Copy as picture’ trick, paste the picture into a new worksheet and submit that.
Uneditable, I claim.
There seems a limit on how big the copied as.. range can be, but you can do it in lumps, paste into the same new workbook, fix them together seamlessly ie same width, no gaps, and weld them together as a group.
Best of luck and I hope they find you not guilty!

Yes, I had this irritation.
I overcame it by going to the ‘Change Links’ box and linked the file to itself.
Everything them became greyed out and no more grief (for this reason, anyway.)

You don’t fancy creating a new column with the AND condition, then using autofilter to list the ones you want? No need to page down thropugh zillions of records then?

Thanks very much, Hans, works a treat.
[Loads of scope for April Fool wheezes here – create someone a Book.xlt they don’t know about, containing a blood-red rectangle about 5 screens big. When they crank up excel, they’ll wonder what’s broken? You didn’t see it here first!]

Text to columns should also work, the delimiter between words being a space.
But will there be a problem about the max number of columns in a worksheet, which might be 255?
Various inelegant workarounds come to mind (split source file into 255 word lumps, eg) but no douibt there’s a hugely elegant way, too.
Why are you doing this? – I’m curious!