Populate Autograph Formula with airSlate SignNow

Add populated text

in this video i'm going to show you how to create an interactive drop-down list in microsoft excel that automatically populates text in other areas of your form we're going to do this using a simple vlookup formula and in this example we're going to use an invoice that actually is linked with a vendor contact list so that i can pull in that vendor's mailing address and contact information into the invoice let's take a look all right so what we're going to do is we are going to create a drop-down list just like this where when we want to set up the bill to information we're going to select the vendor and then that vendor's contact information and mailing address will automatically populate on your invoice once you have that you can actually hit file and then print and you can print to pdf and this will save your invoice as a pdf that you can then send to that vendor so um to do this it's fairly simple to actually set this up so we're just going to walk through exactly how to do it from scratch so the first thing you're going to want to do is include in your invoice template another tab where we have your actual vendor list and this can be a database that you pull from somewhere and paste into your spreadsheet or you can create it and type it however you need to create your vendor contact list but here we've included a vendor name their contact their address city state and zip phone number and email address and so you may have you know several different rows of information that you want to include on this list and what you'll want to do is once you've created your spreadsheet click on any cell within the the spreadsheet that you have data and you hit control t and it will turn that into a table and so once that becomes a table it allows that drop-down list that you're going to program to become more interactive and so as you add new rows or make updates maybe you make a correction to a contact name or something like that on your spreadsheet once that's linked into the drop-down list then all you have to do is make your update here on this tab and it will automatically update the drop-down list on your invoice so let's go over to a blank invoice here and look at exactly how we're going to set this up so the first thing we're going to do is we're going to create the drop-down list here and to do that we're going to come up to data come over to the data tools the drop-down and select data validation from here under the allow select list the source put your cursor in the source and then come over here to the vendor list and we're going to select column a and that'll select the entire um column a and that's going to be the vendor name so it's essentially creating a drop down list of all the different vendor names that you have over here on this tab of the spreadsheet and then just click ok and so now i'm going to highlight this just so that it stands out a little bit that we know this is an input column you don't have to keep it highlighted you can keep it plain and simple with no fill color however you want to do it but just for the purposes we want to make sure that that's filled in so here you can see now we have our drop down list where we can select um the different vendors from that column of information so we've got our drop down list and so now what we want to do is insert vlookup formulas to insert the rest of the information that we want to populate on our invoice like their address and phone number and so forth so the way we're going to do that is we're going to insert a vlookup formula if you've never used vlookup it's really easy to use come to the function library under the formulas tab and under the lookup and reference formulas scroll all the way down until you find vlookup once you use it it will show up in your frequently used list here that you can select from the value that you're going to put here is going to be this cell of the drop down cell a10 so we want uh the what we want the vlookup formula to do is look to that cell as your reference cell then the table array is going to include the entire vendor list here so we are going to select all of the columns in the spreadsheet here then what we're going to do the column index number is the counting the number of columns over which column do you want to return and so the first column we want to return is the vendor contact name that's column b that's the second column over so we put number two there and then for range lookup the way vlookup works we're going to put false um because we want the exact value to be returned and that's part of the vlookup formula and then we'll just click ok and right now it's going to show us in a because we don't have anything selected but once we have um one of our items selected it will show up um appropriately so now what we want to do is include we want to copy that vlookup formula down and include for the street address the phone number and all those other columns of information to appear below so all we have to do to do that is select the cell that we just placed the vlookup formula in and we're going to copy it come up here to our window up here and hit ctrl c to copy i place my cursor at the end and hit enter and then i'm in the cell below and in the cell below i'm going to double click and i'm going to paste that formula but i'm going to replace number 2 with number 3 because i want column number 3 to return there and i'm going to hit enter and then that is going to bring back the street address because if you remember column number three is the address then we're gonna we're gonna paste this formula we're gonna do column four and five and six so we're gonna come up we're gonna copy our formula we're going to place our cursor at the end hit enter we're going to come down here we're going to double click inside and paste and we're going to replace this with number 4 and we're going to come to the end with our cursor and hit enter then we're going to double click in here again we're going to paste and we're going to replace that with number 5. come to the end and hit enter and then finally we're going to come in to the last cell we're going to paste it again replace that with number six place our cursor at the end and hit enter so now we have all of our vendors contact information in the form easily selectable so you can select the next row and all of their information will populate onto your invoice for you again don't forget you're not going to send the spreadsheet out to the client obviously you're going to save it as a pdf or just as a single spreadsheet version without the vendor list database on it and that way you can send them their single customized invoice with their contact information on there if you found this video helpful be sure and give it a thumbs up to like it you can subscribe to my channel and click the bell to receive a notification every time i post a new video visit my website sharonsmithhr.com thank you so much for watching and i'll see you next time