Excel help needed

adelburn · 30 May 2008 at 18:56

I'm having abit of trouble working out what the best formula would be to resolve a problem I have.

Sheet1 has a list of deliveries on and is sorted by postcode and branch. So A1=Postcode, A2=Branch

Sheet2 has a list of postcodes assigned to each branch. So all Birmingham deliveries should go to B1, B2, B3, all Chester deliveries go to C1,C2,C3 e.t.c

If a Birmingham delivery was correctly booked on Sheet1 with a postcode of B1, but assigned to the incorrect branch i.e. Chester, what would be the best formula to use to show this?

I hope that makes sense.

whitecrook · 30 May 2008 at 19:39

This sounds terribly messed up. If sheet 1 had postcode(of address?) and branch, (and presumably a package number of sorts?) and sheet 2 has postcode areas served by branches and the branches listed themselves, why do you need a relationship between them 2 when each sheet has the branches on? ?

Also, you are using excel, when really you should be using a relational database model.

How can a delivery be 'correctly booked' and how is it detected if it is not correct? And how is it assigned to a branch?

adelburn · 30 May 2008 at 20:21

whitecrook said:
This sounds terribly messed up. If sheet 1 had postcode(of address?) and branch, (and presumably a package number of sorts?) and sheet 2 has postcode areas served by branches and the branches listed themselves, why do you need a relationship between them 2 when each sheet has the branches on?

Also, you are using excel, when really you should be using a relational database model.

How can a delivery be 'correctly booked' and how is it detected if it is not correct? And how is it assigned to a branch?

The bookings are made via a terminal emulator and a report generated at the end of the day with all bookings made. As the branch is inputted first, it is possible for say a Birmingham job to be incorrectly booked to Edinburgh, I just need a quick lookup to see if the postcode has been booked to the correct branch.

I dont need anything too complex as it's only supposed to be a workaround for a week or so.

Cheers for any help you can give

Ace Modder · 30 May 2008 at 20:24

you need to use an IF formula.

Let me go setup the spreadsheet and see how it can be done!

Participant · 30 May 2008 at 20:25

On sheet2:

=if(vlookup(Sheet1!A1,A1:A2,2,false))=Sheet1!A2,"OK",CHECK")

or something like that as I'm not totally sure how your data is arranged.