IF formula?

[redneck joe]

Folks – looking to automate a task.

We have geographic zones assigned to field personnel based on our customer’s zip codes.
Our customers change every day.
Our geographic zones do not change.
I want to have our employees’ tech number automatically placed in the cell (same row) as the customers’ zip code if it is in their area.


Is it possible to create a zip code list and have the cell automatically populated with the field employee’s number based on the zip code list? I have attached a small file that has our zip code list format as well as the customer base info.

I have some experience with IF formulas but this one seems tricky.

[dcgrove]

Your file does not have the zip codes that each tech works in. With out that I can't come up with a solution for you. Is it correct to assume that the zip code is going to be typed in when a new customer is added?

[redneck joe]

the zip codes are in the top section of the sheet above the customer data.

[dcgrove]

This is my quick and dirty solution.. I am sure some of the experts on here can put together something much more elegant. I inserted another worksheet and basically created a list of your zip codes from the top section and the tech name next to each one. Then I named the entire range "techzip". This formula is in cell I17 and can be copied down the column.

=IF(ISNA(VLOOKUP(F17,techzip,2,FALSE)),"",(VLOOKUP(F17,techzip,2,FALSE)))

However, your zip codes in your customer lists and the zip codes in the tech zone tables do not match which confused me at first.


Hope this help!

Clayton Grove

[modytrane]

Your example didn't have matching zip codes.
See attached example. Column K has the formula to assign tech based on matching the zipcode in column F with the array under each tech.
I added one zipcode in F7 to give you a match.
modytrane

[redneck joe]

However, your zip codes in your customer lists and the zip codes in the tech zone tables do not match which confused me at first.

Clayton Grove

my apologies. The actual sheet has 10 tabs for our different locations and about 300 records each and in trying to thin down I used disparate data.






I'll give this a whirl and see what happens.

[dcgrove]

if all of your data is setup like your example I would use the solution that modytrane posted.

[redneck joe]

if all of your data is setup like your example I would use the solution that modytrane posted.

yes it looks like that one will work best as it is all contained in one tab. We will test tomorrow on a full blown day and report back.

[modytrane]

See the attached file.
I made a few enhancements that might be useful.
1. conditional formatting to highlight unassinged zipcodes.
2. K3:K14 will list all unique unassinged zipcodes. This can give you a quick guide as to which codes need to be assigned to one of the techs.

Note: column T is being used as helper column. It has a list of all codes unassinged. Some of these are repeats, so from this list, I am creating a list of unique entries in K3:K14.

Hope it works out for you.
modytrane.

[redneck joe]

See the attached file.
I made a few enhancements that might be useful.
1. conditional formatting to highlight unassinged zipcodes.
2. K3:K14 will list all unique unassinged zipcodes. This can give you a quick guide as to which codes need to be assigned to one of the techs.

Note: column T is being used as helper column. It has a list of all codes unassinged. Some of these are repeats, so from this list, I am creating a list of unique entries in K3:K14.

Hope it works out for you.
modytrane.

works perfectly. We added to our full workbook yesterday and all is good. I just had to expand the formula to cover 10 zones rather than the two on my example.

thanks fo rthe help....