help in finding a function for vlook up

Hello
i have the attached table that i use to enter the shipper's name on a Bill of lading, and i have a list of shippers (address + all info) in a different page. how can i pull the desired shippers info into the main page.
i tried V lookup but the info is set up in different rows and columns as seen on the attachement, any formula or keyboard shortcut, in which when I enter the first letters will I get a list of option to choose from? the List option would not work as it will not populate properly. or something similar that will help me speed up this process.
any advice will be greatly appreciated.

Hi gullit,

Your question is an interesting one, and showcases the value of a couple of cool features in Excel, including Drop-Down Boxes (using Data Validation) and the VLOOKUP function... I've written up a brief tutorial that shows how you can take a spreadsheet similar to your Bill of Lading and make it automatically fill out the form using the address data on a different sheet. The how-to is here:

exceltactics.com/how-to-automatically-fill-out-forms-using-data-validation-and-vlookup/

Let me know if it helps solve your problem!

Good luck!
Andrew

Thanks a lott! this is just perfect.
3- questions:
1- can't seem to find the option to protect formula
2- eventually, the data base for shipper will grow, is there an option in which i imput the first few letters of the shipper and the list will narrow? or enter the initial and all shippers with that letter will show in the list (in order to quick select them)
3- i have entered 1 column for address 2, but now if there is no second address (suite, bldg, etc.) present it shows a 0 value, how can i make it just blank?

thanks for your help and i feel a little bit wiser thanks to you!

Hello,

In answer to your questions:

1. What do you mean by "protect the formula"?

2. There are only a couple options. Excel doesn't support auto-complete by default. This is the closest option: ozgrid.com/Excel/autocomplete-validation.htm

3. Place an apostrophe in the empty cell when it's blank, and it will show up as empty when VLOOKUP finds it.

Good luck!

hello

1- what i mean is how to protect the cell in order that the formula may not be modified or deleted by mistake
2-those are good options to keep in place, but i also found another option using visual basic and the combo box, i will give it a try. link to it below just in case.
contextures.com/xlDataVal10.html
3-apostrophe works!!
Thanks a whole lot for your help, it has been really helpful, there is nothing better than to visualize something and then see it come thru

Glad that the apostrophe worked!

Cells are typically "locked" by default, which means that when you protect the sheet, they will not be changeable. To check for sure, right-click and choose Format Cells...

In the Protection tab, check the "Locked" box to lock the cell. This won't have any effect until you Protect the entire workbook.

To enable Protection, Go to the Review menu tab, and click "Protect Sheet" or "Protect Workbook". Add a password or leave it blank and click OK. With the sheet/workbook protected, those locked cells will be un-changeable.