I have the following table in Sheet1, which will be a password-protected worksheet:
A1: ZIP Code B1: City Name
A2: 8272 B2: Manila
A3: 6225 B3: Quezon City
A4: 2986 B4: Caloocan
A5: 1632 B5: Zamboanga
In Sheet2, I have the following column headers:
A1: Select ZIP Code B1: City Name
Basically, in Sheet2, cells under column A are drop-down lists containing the ZIP codes in Sheet1. Once a user selects a ZIP, the corresponding city name should appear to the right, just so the user can visually confirm that he/she entered the correct ZIP code.
I've managed to retrieve all the ZIP numbers from Sheet1 and put them in drop-down lists to all cells from Sheet2!A2 downwards. My problem is: In Sheet2, how do I display the corresponding city name in B[rownum] based on what is selected in A[rownum]? I tried using VLOOKUP but can't seem to dynamically point to the look-up value (the first argument in VLOOKUP) across all the rows beneath Sheet2!B1. I need a formula, not VB code.
