Original subtitles

Let's look at this spreadsheet.

We have some ticker symbols in this column here and then a bunch of financial data,

then we'll get to in a short while.

Looking at this ticker symbol,

we can see that each ticker symbol is repeated a few times.

We want to create as part the data validation is a drop-down list that

contains only one occurrence of each of these ticker symbols.

To get started, we're going to use pivot tables.

Well, there are many ways to create data validation drop-down list.

This is one of the ways.

So, we select ticker symbols,

you go insert, fill the table,

we've selected the cell already,

and we say new worksheet,

and it creates a pivot table here for us.

We select ticker symbol,

and it shows up under the rows,

and you can see any repeated occurrences of each ticker symbol had been eliminated,

and each symbol shows up only once.

We need access to this unique list of names.

I'm going to go ahead and paste it in here,

and then once I've pasted and I'm going to go ahead and select it again,

and use name box to give this list a name called company list.

So, I'm going to have the selector,

go to name box here and go to company list.

Once I've done that, I can reference this list by

simply calling this name in my formulas.

We can now go ahead and create a new worksheet here.

We're going to call it data validation.

I'm going to type in ticker symbol because I want this to show up next to that,

and then in this cell,

I'm going to go Data, Data Validation.

You have the option to choose various criteria.

So, I'm going ahead and select a list,

and as a source,

I'm going to reference back to this list here,

and I can see it automatically filled into named box,

and I go ahead and click Okay, and there you have it.

You have a data drop-down menu that only references each of these names separately.

Another trick I just want to mention is that if you find

that you're having problems with the data range it's not showing up correctly,

you can go to the formula tab at the top,

and you can go to Name Manager,

and here you can actually see all named boxes that you have,

and it tells you which file it's referring to,

and you can confirm which ones you want to delete,

which ones you want to edit,

or you can use the edit option to do that and correct the reference,

which sheet did refers to.

So, this is also an option if you're having some trouble with

the Data Validation. See you in the next video.