How to create a vlookup referring to another spreadsheet

What we are going to do today is reference another work sheet in your work book using a vlookup

What we need to do first is create a reference point for our range of data that we want to look up

The way that we do this is by creating a defined name

How to define a name of a range of data

You need to first open up the work sheet in the workbook that you are going to reference.In our case we are going to open up the stock spreadsheet.

You need to highlight the data highlighted in the screenshot below

This highlighted data we are going to use in our vlookup.

Select formula tab-select define name tab

Now we are going to encompass the range we have specified into a vlookup formula.The reason that we are going to do that is because we are going to put a price in the total cell for whenever a item code is selected

The formula that we are going to use says:

1.look up the item code in sheet b ,

2.go to sheet a to find it from the datarange we have specified,

3.go to column 3 of that range to locate price we want

So now we need to select the sales with drop down list tab of our work book

This formula says –find the value of item code 4 in b3, but go to sheet a to find it.Then look down column 3 in sheet a ,to find data

Now if we wanted to work out the total price incorporating the vat

We would have to do this

Define the name in sheet a as pricewithvat

Then what we would need to do with our look up is specify the range we have just created as above in our new formula

Now we need to go back to sheet a and put in some formulas so that it works out the total

=vlookup(b3,pricewithvat,5)

This will then return a value to the total with vat column

Stage 3-create a drop down list then do a vlook up referencing it

Open up tab in work book called drop down with v look up

What we are going to do is create a drop down combo box to list numbers 1-10.

The numbers 1-10 are going to be referenced in the stock sheet

Go to cell b3 and select developer tab-then select combo box

Draw a combo box on your spreadsheet cell b3

Now left click away from the combo box so it is unselected

Now right click it and select format control

Now we need to select the insert range field.Now select the grey box on the end

It will then look like this

Now we need to select the first worksheet called stock (where we previously defined a range)

Now we are going to link the data back to the cell that we are putting the combo box in

This will then mean that whenever someone selects a different price from the drop down it automatically selects the new price

Make sure that you select drop down lines of 10 not the 8 that currently exist

Now whenever you change a figure in the drop down the numbers will change in the total

7 | Page