I have records of deliveries made to customers. Column headings include
depot (a 4 digit id number), customer number, customer name and address.
Each day I manually type in the time taken and distance travelled for each
delivery. These values are preset and do not change. Most of the deliveries
are made from the same depot to the same customer, but occasionally when
product is unavailable the depot of origin will change. I would like a
method of checking customer number and depot of origin and inserting values
in the time and distance columns.
I would appreciate any suggestions.
--
lyn

Lyn

See Excel Help on Vlookup.

Using this, what you could do is set up a separate Time and Distance table
which your input sheet references. On a seperate sheet you can list all the
know Time and Distance values. You will need to do this by Depot and Customer
as a concatenation i.e if DepotID is 2087 and customerID is 123456 you enter
in column A: 2087_123456. In Column B you have the time and in Column C the
Distance.

For the purposes of this example I will assume that on your input sheet
DepotID is in column A and customerID is in column B. Add a new column C and
in Cell C2 enter the formula =A2&"_"&B2. Copy this formula down as far as you
are likely to enter data on the sheet. You can then hide column C.

Assuming column D is your time field, in cell D2 enter the formula
=VLOOKUP($C2,Sheet2!$A$1:$C$15,2,0).
Similarly in Cell E2 for the distance you can enter:
=VLOOKUP($C2,Sheet2!$A$1:$C$15,3,0)
Copy these formuas down and you should see the results.
If the combination of DepotID, customerID does not exist you will get an #NA
error. You can avoid this by changing the formulas to
=IF(ISNA(VLOOKUP(C2,Sheet2!$A$1:$C$15,2,0)),"",VLO OKUP(C2,Sheet2!$A$1:$C$15,2,0))
mind the wrap.

This is not meant to be a lesson on vlookups (if it was it would surely
fail) but a pointer as to how you may accomplish this. As stated at the start
check out Excel help on Vlookup.

Hope this helps
Rowan



"lyn" wrote:

I have records of deliveries made to customers. Column headings include
depot (a 4 digit id number), customer number, customer name and address.
Each day I manually type in the time taken and distance travelled for each
delivery. These values are preset and do not change. Most of the deliveries
are made from the same depot to the same customer, but occasionally when
product is unavailable the depot of origin will change. I would like a
method of checking customer number and depot of origin and inserting values
in the time and distance columns.
I would appreciate any suggestions.
--
lyn