Using to and from zip codes to locate a single sheet

Greetings.

Hopefully, someone will have an elegant solution to this.

I have a large spreadsheet with multiple tabs. Each tab corresponds from a shipping area, like Chicago, to another area, like Saint Louis. We ship from and to most, but not all, zip codes in a given metropolitan area.

Currently, the first sheet has hyperlinks to all of the from/to combinations, but the user has to know where to go and it's easy to make a mistake for a new user. What I would like to do is give the user a chance to put the From and To zips in two different cells and then do some sort of operation that will look at the first three numbers in both zips, then link to the right from/to sheet.

Off the top of my head, I guess it could be done through a laborious vlookup of a table of hyperlinks combined with nested IF statements, but that seems like the sledgehammer approach.

Any thoughts?

Can't you use the Hyperlink function and concatenate the first 3 letters of each code somehow to link to the proper sheet?

Assue A1 = Zip (from) , B1 = Zip (To), and your worksheet name is like "123 to 456"



Put this formula in C1

=HYPERLINK(CONCATENATE("#'",LEFT(A1,3)," to ",LEFT(B1,3),"'!A1"),CONCATENATE(LEFT(A1,3)," to ",LEFT(B1,3)))