Difficult for me, probably basic to you

[justjohn]

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 code, 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.

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 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

[Chris Ferguson]

Try looking at vlookup in the help. It will do exactly what you want.

Chris


"justjohn" <[email protected]> wrote in
message news:[email protected]...
>
> 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 code, 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.
>
> 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 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
>
>
> --
> justjohn
> ------------------------------------------------------------------------
> justjohn's Profile:
> http://www.excelforum.com/member.php...o&userid=30663
> View this thread: http://www.excelforum.com/showthread...hreadid=507223
>

[Arvi Laanemets]

Hi

It looks like Word's Mail Merge would be a best choice. Be sure that the
source table is on the first sheet of workbook, and has a single header row,
that there are no gaps (empty rows) in table, and be sure that the workbook
is saved. Open Word, and create a Mail Merge document - point to your
workbook as source table, when asked (don't forget to change file type -
otherwise you don't find it).

In Word you have the far more possibilities to design jour job card (using
text formatting as you like, using tables with hidden borders align texts,
etc.). When you are finished with design, you can easily generate a word
document, where every employee's info is on separate sheet(s) (the number
of sheets for an employee depends on your Mail Merge Document design),
orsend it/them directly to printer. Before generating/printing Mail Merge
document, you can estimate which rows from table are processed, or determine
filter conditions for source table (p.e. you can determine that job card for
row 24, or for rows 1-10, in your table is generated, or that a job card for
employee with Name="John Smidt" is generated, or that job cards for
employees, for which the field HireDate is bigger than some date, are
generated, etc.)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )


"justjohn" <[email protected]> wrote in
message news:[email protected]...
>
> 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 code, 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.
>
> 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 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
>
>
> --
> justjohn
> ------------------------------------------------------------------------
> justjohn's Profile:
> http://www.excelforum.com/member.php...o&userid=30663
> View this thread: http://www.excelforum.com/showthread...hreadid=507223
>

[justjohn]

Thanks Chris, I will look into vlookup and hopefully lear something

John

[JMB]

Additional information on Debra's site.

http://www.contextures.com/xlFunctions02.html

"justjohn" wrote:

>
> Thanks Chris, I will look into vlookup and hopefully lear something
>
> John
>
>
> --
> justjohn
> ------------------------------------------------------------------------
> justjohn's Profile: http://www.excelforum.com/member.php...o&userid=30663
> View this thread: http://www.excelforum.com/showthread...hreadid=507223
>
>

[justjohn]

Thankyou both very much for the advice. I have started to use the vlookup function and it appears to be at least part of the way forward for me.
There appears to be two problems still:
The vlookup function requires the input of the the cell "coordinates" this would be very useable if all of the account numbers in the company database were; 1, all ONLY numbers, and 2, an unbroken sequence of numbers.

Also, it seems to require that I write a function each time I produce a Jobcard, and I need to run several of them.

So, what I really need is a function that will search column A for a given account number (which could be alpha, numeric, or alpha&numeric), then insert the contents of some of the cells in the row of the "found" account number into prescribed cells on a different page. The second part of this appears to require the "linking" of several lookup functions.



Please don't think I am feeble and am just scrounging information, I'm not. I am keen to learn as much as I can about excel, and take the information that you give me seriously, and am grateful for it too.

thankyou both again

regards

John

[JMB]

Your lookup table can be a combination of alpha and numeric data. One thing
you do have to make sure is that the data on your job card has (that you are
using as the first argument of VLookup) has to be the same data type as its
match in your Lookup table (which is in Column A).

I've seen data get imported into Excel that appears to be numeric, but is
actually text ( "5" is text, but appears to be a number). Vlookup will not
match 5 to "5" unless you use some other excel functions (such as TEXT).
Also, leading and trailing spaces could cause the data to not match (look at
excel help for TRIM function).

You will need to key a Vlookup function for each field you want a value
returned. In some cases you may need to concatenate several VLookup
functions (City, State Zip - if this data is in different columns in your
lookup table, but you want it to appear together in one column).

Once you have the first job card set up, maybe you can use it as a template.
Put a button on the sheet and assign a macro like the following to make
copies of the original sheet (you'll need to change the sheet name in the
macro to your own sheet name).

Sub CopySheet()
Sheets("Sheet2").Copy After:=Sheets("Sheet2")
End Sub


"justjohn" wrote:

>
> Thankyou both very much for the advice. I have started to use the
> vlookup function and it appears to be at least part of the way forward
> for me.
> There appears to be two problems still:
> The vlookup function requires the input of the the cell "coordinates"
> this would be very useable if all of the account numbers in the company
> database were; 1, all ONLY numbers, and 2, an unbroken sequence of
> numbers.
>
> Also, it seems to require that I write a function each time I produce a
> Jobcard, and I need to run several of them.
>
> So, what I really need is a function that will search column A for a
> given account number (which could be alpha, numeric, or alpha&numeric),
> then insert the contents of some of the cells in the row of the "found"
> account number into prescribed cells on a different page. The second
> part of this appears to require the "linking" of several lookup
> functions.
>
>
>
> Please don't think I am feeble and am just scrounging information, I'm
> not. I am keen to learn as much as I can about excel, and take the
> information that you give me seriously, and am grateful for it too.
>
>
> thankyou both again
>
> regards
>
> John
>
>
> --
> justjohn
> ------------------------------------------------------------------------
> justjohn's Profile: http://www.excelforum.com/member.php...o&userid=30663
> View this thread: http://www.excelforum.com/showthread...hreadid=507223
>
>

[justjohn]

thanks JMB

that's a whole lot for me to think about and explore.
Thanks very much for pointing the way.
Please, watch this space

regards

John