Difficult for me, probably basic to you

[onlyjohn]

This one must be very easy for someone who knows his or her way around
Excel, clearly I don’t, (though I have tried) or I would have made some
progress myself.

I have had the pleasure of designing a job card dumped on me at work.
I’ve managed that much. However filling in all the fields is tedious
and slow even with data validation/drop-downs. Then, I thought to
myself; all the data required for the cells in the job card is in an
excel spreadsheet in columns headed:-
Account number, Name, Address1, Address2, Address3, Postcode, Phone,
Make, Model, VIN, Date of purchase.
Therefore, it must be possible to achieve some sort of “auto-completeâ€
status.
Consequently what I would like to do is type an account number into
cell A1 on sheet 2, then, on pressing the "enter" key, and by the magic
that is excel programming all relevant details would appear on sheet 1:-
the customer’s name will appear in cell B5, Address1 will appear in B6,
Address2 will appear in B7, Postcode will appear in B8, Phone number
will appear in B9, Make in B11, Model in B12, VIN in B13, and Date of
purchase in B15.

I have found the vlookup function and can use it (to some extent anyway)
but this requires the input of the coordinates of a cell, whereas I want to
search
using just the value within that cell. The value found in the account number
field
could be alpha, numeric or alphanumeric.

So I have turned to you clever friendly folk for any help or advice you
can give.
I must stress that cutting, pasting and a bit of data validation and a bit
of
vlookup shows the limit of my knowledge of excel.
Consequently simple instructions would be greatly appreciated.
Thank you very much for taking the time
to read this.

John

 

[Arvi Laanemets]

What about my advice to use Mail Merge? It looks like the source table for
it have you already.

excel spreadsheet in columns headed:-
Account number, Name, Address1, Address2, Address3, Postcode, Phone,
Make, Model, VIN, Date of purchase.

You only need to design the mail merge document for job cards, and it's all.
At every time you can generate or print a job card for any employee in
table.
You can have several different mail merge documents for this table designed
too - p.e. to print employment contracts etc.

Here is an step-by-step instruction.

You must have an Excel table, where all employee's info you need is on
single line. All info pieces must be in separate columns (i.e. when you need
both Forename+Lastname and Lastname only in job card, or in any other
document you want to generate from this table, the ForeName and LastName
must be in different columns). Be aware that all fields from table are read
into document exactly as they are in table - you can't use formulas in mail
merge, except a couple built-in ones like for displaying current date and
time etc.
The table mustn't contayn any empty rows - you can't access rows below empty
one. The same for columns.
All columns MUST have headers.
The sheet with table must be first in workbook (sheet's tab is leftmost
one) - it is easiest way to qurantee, that the table will be accessible from
Mail merge.
The workbook must be saved. You even can close it now.

Start Word. Select from menu Tools>Mail merge (When you never used it
before, the probably it is hidden, press >> at bottom of Tools menu window
to display hidden options).

In Mail Merge Helper window, click on Create button. Select 'Form Letters'
from list. When you started with empty word document, then click on Active
Window button, otherwise on New Main Document button.

You are back to Mail Merge Helper window now. Press Get Data button, and
select 'Open Data Source' from list. In Open... window, set file type to MS
Excel Worksheets (*.xls), locate your workbook, and open it. Select 'Entire
Spreadsheet' and press OK.

In window opened now simply press Edit Main Document.

Now design your mail merge document (practically it works as template, which
is use to create outputs later). All texts common for all employees, you
enter directly into main document, whenever you need to insert a field,
select it from dropdown 'Insert Merge Field' at left top of Word's window
(below menus). Column headers in Excel teble serve as field names here.
Inserted fields look like <<FieldName>> in main document. NB! In generated
document, instead of field, according entry from table is inserted, and all
text adjacent to it is adjusted automatically - depending on the real width
of entry in table.
You are free to use any Word features to improwe the lookout od your mail
merge document.

When you are finished, save the main document under some reasonable name,
like MMDoc_JobCard. When you did have the Excel workbook named like
JobCards.xls, it will be fine - so you are able easily find workbook
attached to this particular MM main document any time when you need it.

Now let's us try to generate some job card. With main document opened,
select Mail Merge from Tools menu again. In Mail Merge Helper window, you
can activate Query Options window now, but we skip it now - you can look it
over later, but all those options will be available later too. So press
Merge button instead.

In Merge window, you can now:
a) determine the output:
1. New document - a new Word document is created - one or several
pages for every employee (every job card starts from new sheet);
2. Printer - job card(s) is/are sent directly to printer;
3. Electronic Mail - every job card is sent to different addressat
as a mail. When e-mail adresses were entered into your tabe, you can point
the field, which contains them - otherwise you have to enter them manually
for every document. NB! When you don't use Outlook as your mail client, this
feature may not work.

b) you can determine which records (by record number count) are processed.

c) set query options. Here you can filter your table, determining by field
values which records from Excel table are processed. And also you can
determine here, in which order selected records are processed.

When all settings are OK, press Merge button. The job is done!

When you next time need to generate some job cards, simply open proper main
document (MMDoc_JobCard.doc in my example). Unless you moved main document
and it's source file elsewhere meantimes, the attached excel workbook is
opened automatically (otherwise you have to open the source file yourself).
Select Tools>Mail Merge, press Mege button, adjust all mail merge settings,
and press Merge to generate job card(s). It's easy, is it?

NB! Any changes made in Excel table, when mail merge document is open, don't
be available for mail merge until you save the Excel workbook, and reload it
into mail merge. Easiest way to do it, is simply close MM document and
reopen it again.


Arvi Laanemets