Replies

Yes, it would certainly help to see sample data, otherwise we have no idea what you’re working with.

I hope this is helpful. I’ve attached a workbook with dummy data, in real life two workbooks with multiple sheets are used. Don Well’s assumption hold true – every module code is of the form 9ABC?123, (the ? is either a digit or a letter). So here goes with the problem:

My university offers courses for healthcare professionals working in hospitals in England. The hospitals are called Trusts, and buying a place on a course is called ‘commisioning’. Each course is called a Module.

I have a list of module applications, from each Trust.
For each module code, I need the the number of people wanting a place from each Trust
This number goes into the corresponding row of the ‘places per module’ sheets, one sheet for each Trust.

‘Applicants for places’ is one workbook, with a sheet for each of the (several dozen) Trusts. We get a new workbook every month, with all that month’s new applications.
‘Places per module’ is a separate workbook, with a sheet for each Trust, the list of modules is the same for each Trust. Here we log the new applications in the column for that month, and it keeps the running total.

By hand, we can run down the applications list by eye, and tally the rows with any given module code. The total goes into the appropriate cell in the sheet for that Trust, ie row for the module, column for the current month. We have to do that for each module code, for each Trust in turn, every month. There are over a hundred modules, several tens of trusts, and many hundreds of students over the course of a year.

The relevant column in ‘applicants for places’ is CF Module Code, the other column are not of interest here.
The match is with the value in ‘Code’ (col A). The total goes into the current month column.
Sometimes the col A value is the module code, sometime we find more than one code as some courses have two code attached to it. It would be possible to create a separate row for each in these cases if that made the VBA easier to create.

Thanks in advance, Frank.

This looks to be turning into a big (for me) project.

I’ve broken it down into several chunks, the first is to locate a row where col A contains the course code, then find the column for the current month.

Next, locate a specific column in another workbook and count the number of times that course code appears – in a column containing many different codes.

This total then should appear in the current month column of the first workbook.

I’ll create some dummy wsheets over the weekend and post them if that would help folk get a handle on things.

Frank

Hans, thank you very much for this, I shall try it out.
Unfortunately, the originator did not know we were going to import into Outlook, and would not have known the correct format to use in any case.
Plus this is a one-off and *should* never have to be done again
Frank

All times have four digits, 0900 or 0830 etc. in a few cases, a period is included 09.00 but never a colon.
Also there may or may not be spacing around the hypen, or only a single four digit time is included ie no end time.
Thanks Hans, it feels good to have you on the case.

Thanks Hans, that lovely.

Thanks, I may go the VBA route if it gets too annoying.
( We are all having great fun here at the office comparing experiences with the new GUI, inversely correlated with productivity)
Frank

Select Tools | AutoCorrect options… and activate the AutoText tab.
Alternatively, select Insert | AutoText | AutoText…
Clear the check box “Show AutoComplete suggestions”.
Click OK.

Unfortunately, this will turn off ALL autocomplete suggestions, including those for AutoText entries. As far as I know, there is no way to turn of AutoComplete for specific items only.

Hi Hans,
I forgot to mention we have all been ‘upgraded’ to the 2007 version of office, and all my Tools have been taken away. I had a look through the Word Options part of the Button but can’t find autocomplete checkbox.

Thanks Hans, that worked perfectly. Frank

Are you sure it’s an island? From Wiki:

In any case, since the criteria was the amount of time necessary to travel to a city, you would need ‘someone’ to do just that. Something that can’t be done, since nobody is there.

Bouvet Island is visible (some of the time at least – weather permitting) from google earth. It is in the advertised spot and appears to be an island.
I propose a trip to the island, and from there to any city of your choice. I am willing to do the travelling, if you organise everything and provide financing.

Er, which part of the following did you miss from my opening post:

But if you really want a prize, I offer two low calorie chockie bars! –> For second place

For inaccesability, it would be hard to beat Bouvet Island ( http://en.wikipedia.org/wiki/Bouvet_Island ), the place most distant from any other land mass, in this case antarctica. It is also almost impossible to land on the island when you arrive, either by ship or air.

Hi Frank,

To get the link to update to point to whatever the current folder is, you’ll need to add the macro attached to my [post=”670027″]Relative Paths Star Post[/post] to your Word document.

As an aside, if you name the linked range for a given table in Excel, you then then tell Word to use that range for any updates. This can be useful if the number of rows/olumns in the Excel range is liable to change. On the Word side, all you need to do to implement this is to:
. select the linked data
. press Shift-F9 to expose the field code, which will look like { LINK Excel.Sheet.# “C:\Users\ … \Analysis.xls” “Sheet1!R1C1:R5C5” a r }
. change the field code to { LINK Excel.Sheet.# “C:\Users\ … \Analysis.xls” “RangeName” a r } where ‘RangeName’ is your Excel range’s name.
. press F9 to update the field.

Thanks guys – stereo replies! I’ll work on it over the weekend.
Frank

Perfect Hans, this does exactly what I need.
Many thanks, Frank

This is a further query to my one of last July. I am now trying to change the field code so that it picks up from a workbook in the same folder as the word doc, even if the two files are moved around ie into other folders/computers. I want the pair to be ‘portable’ between users, and thought I could edit the field code to start with ‘..workbookname etc to refer to a file in the same folder. I can’t tell if this worked or not! The name of the files will not change across users, but the data content will. Any help gratefully accepted. Frank

I don’t have Outlook 2007. In Outlook 2003 one can Filter Contacts list through the View menu, then select all the Contacts displayed in the resulting View, and then use Message to Contact; all the selected Contacts are included on the To: line. In the Outlook 2003 Contact View Filter settings, the “More choices” dialog allows selection of multiple Categories. Can you test that in Outlook 2007? If it works, note that you can permanently save Filtered Views under new View names and call them up through the View Menu.

(If the Category memberships are as volatile as you describe, managing them will always be a burden.)

Hi John, the method you describe is the one I currently use. Displaying the contacts grouped by category also works. Once I have a blank email with the To.. field populated by 30 or 40 names, I copy them to the email I am forwarding and paste into (usually) CC and then send it. This is so that the attachments get forwarded – lazy me just wants to save a few mouse clicks! I have not found a more efficient way but hope the loungers might know a trick ……..
Cheers.

Why not use distribution lists with easy to remember names? However, you have to remember to update the distribution list members when there is a personnel change.

Joe

Hi Joe, I don’t use dist lists for just that reason. Contacts may be in several, many or far too many categories, and the categories change almost daily – ie which project or phase a person is in. I can just about keep up with maintaining a contact and it’s categories, using the open contact form but found DLists to be unworkable. The amount of traffic is increasing, up to now I could cope with the clunky copy and paste method. 2007 won’t allow me to select by category in the ‘Select recipients’ box which open when I click the ‘To… ‘button which would be a great solution.

Thanks Hans
I also see that the monthly view sometimes doesn’t show all the entries for a given day, the square space being too small, and a little arrow appears as if to say ‘more’ which is useful.
Frank