Its been a while since i did this at college so thought id ask here for some help before i dig out the books.

We have a problem at work. We like to give our customers a two hour window when their delivery will be but our office staff dont know the order of the rounds.

What i would like to do is make a simple excel spreadsheet so they can put in the first part of the postcode (ie BN7) then put in the next number in a separate box (these could be drop down lists) and depending on the combination of both postcode parts, it would show what days we deliver to those areas, what time and which driver.

I know its a very long shot but does anyone know where i might find a similar spreadsheet i could edit to suit our needs or how i might make the combination bit work?

just a quick reply, but you could end up with a large nested if statement.
If you knew, for example that a driver was for a particular part, such as BN7 and the second part was related to the time etc it would make it easier.
for example, if the cell a1 had the first part of the postcode (eg, BN7) and b1 had the second (eg, 7AB) you could use something like this in a2

=if(a1="BN7", "Driver1", if(a1="BN6", "Driver2", if(a1="BN5", "Driver3", "no drivers available"))

you could then use a similar statement for the second part of the postcode, giving numerical values for mileage in cell b2
=if(b1="7AB", 6, if(b1="7AC", 7, if(a1="7AD", 8, "too far"))

then base another statement on these values and place this in c2
=if(b2>8, "1hour", if(b2>=7, "45mins", if(b2>=6, "30mins)))

finally link them altogether in cell a4 for example
=CONCATENATE("your driver is ",A2:A2, " the mileage is ",B2, " miles and it should take approx ",C2)

hope that helps to someway link what you want

regards
Ian

Bored in my hotel room

Not quite sure why you want drop downs, or separate postcodes, but I think VLOOKUP is what you want. Try this as a simple example: -

On Sheet1, populate with example data as follows (3 columns): -

BN1 1 Monday 2pm
BN1 2 Monday 12pm
BN1 3 Monday 8am
LS27 1 Wednesday 1pm
LS27 2 Wednesday 3pm

Then, on Sheet2, in A1 type: BN1 2AA
In A2 type: =VLOOKUP(LEFT(A1,FIND(" ",A1,1)+1),Sheet1!A:C,2,0)
In A3 type: =VLOOKUP(LEFT(A1,FIND(" ",A1,1)+1),Sheet1!A:C,3,0)

What you'll find is you can type any postcode in A1, and it'll lookup the first part of the postcode upto and including the first digit after the space in the lookup table on Sheet1.

If I've slightly misunderstood exactly how close a postcode you want then you can adjust to suit, but it should sort you out.


Sy

GP - exactly as per my example

Now modified to only use the first digit from the second half of the postcode

[Edited on 2/12/09 by stevebubs]

PS You have to make sure your data is sorted correctly for the VLookup to work.

Vlookup by default looks for true statements, if you finish the query with false you do not need to sort:

=VLOOKUP(DataEntry!$B$4,SourceData!$A$2:$Z$999,4,FALSE)

Regards Mark

Wow, cheers guys. I was expecting maybe one reply in a day not 6 overnight

Just shows how great this forum is