speedyxjs
|
| posted on 2/12/09 at 09:30 PM |
|
|
Excel Help
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?
How long can i resist the temptation to drop a V8 in?
|
|
|
|
|
tendoshingan
|
| posted on 2/12/09 at 10:10 PM |
|
|
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
|
|
|
stevebubs
|
| posted on 2/12/09 at 10:21 PM |
|
|
Bored in my hotel room
|
|
|
gingerprince
|
| posted on 2/12/09 at 10:24 PM |
|
|
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
|
|
|
stevebubs
|
| posted on 2/12/09 at 10:30 PM |
|
|
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]
|
|
|
stevebubs
|
| posted on 2/12/09 at 10:35 PM |
|
|
PS You have to make sure your data is sorted correctly for the VLookup to work.
|
|
|
mark chandler
|
| posted on 2/12/09 at 11:01 PM |
|
|
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
|
|
|
speedyxjs
|
| posted on 3/12/09 at 07:03 AM |
|
|
Wow, cheers guys. I was expecting maybe one reply in a day not 6 overnight 
Just shows how great this forum is 
How long can i resist the temptation to drop a V8 in?
|
|
|
