Any excel experts out there?

Hamster

Hamster

Hamster

Soldato
Joined
25 Oct 2002
Posts
5,648
Location
location: location
I'm having difficulty finding out how to link values from one worksheet to another.

I'll describe what I'm trying to do using a simple example.

Worksheet 1 has a list of different car parts next to which is a column with the weight in grams and next to that a column with price.

Worksheet 2 has a table with lots of different data. In the centre of the table there is a column for car part name, weight and price.

I've created a dropdown box in the car part name section in worksheet 2 and have linked it to the car part name column in worksheet 1.

What I want in worksheet 2 is for the corresponding values for weight and cost to be automatically added once I've chosen the car part name from the drop down list.

Could anyone please simply explain how I go about doing this?
 
VLOOKUP will do this for you if I understand correctly.

I did this for a dietplan not so long ago, the idea was what ever was entered on Page 1, would drag the information from a list on Page 2, and then bring with it the calories, sugars, carbs and total them at the bottom of Page 1.

=VLOOKUP(C3, 'Food Intakes'!B6: D1000, 3, FALSE) < : D has no space, makes the :D next to each other aha!

C3 is what it looks at for the value, in this case it was 'Special K';
It then looks at the second worksheet 'Food Intakes'! (! Is required to select from another worksheet;
B6:D1000 is the value range where it checks for the calories, carbs, sugars;
3 guides the VLOOKUP to which column it needs to look at in the table;
False, is so it checks the string 'Special K' and returns value for anything where the string matches 100%.

If you have true instead of false, the lookup will pick up other data, for example if you had orange juice and orange. It will take the values of orange instead of looking for the full Orange Juice string.

Hope this helps? :p
 
A simply Vlookup should be what your after here (assuming you want to bring through the price & weight for each carpart at the row level). The only requirement would be for the car part names listed in both worksheet 1 and worksheet 2 to be identical - Excel can be a bit picky sometimes.

Essentially the formula would look something like this (Locate in Worksheet 2, one column after the drop down list column):

=vlookup("Cell you want to retrieve information for - worksheet 2 drop down", "Area of data you are looking up from - worksheet 1 Carpart list out as many columns as required", "column you are looking up to in worksheet 1", FALSE*)


*This is required so it only retrieves the exact value you require.

Also note if you are looking up to data within the same Excel workbook (seperate sheets of the same file) you will need to lock the area of data you are looking up to. If you don't each row you move the formula down to will see a corresponding drop in the area lookup - in effect moving it outside the area you actually want. To do this quickly hit f4 when selecting the lookup area.

Hopefully of use:

http://howtovlookupinexcel.com/
 
Last edited:
What they said, the only thing to be aware of is that a vlookup will only match against the first column in the array you look up against.
So if you have columns: Weight, Part Name, Price in that order you will need to cut and paste the 'part name' column in front of the others.
(If you can't do this you need to use a combination of INDEX and FIND, and it can get unpleasant).
 
Excellent, Reaper & oweneades! I've managed to work out how to do the vlookup and Littlecrow, I'll do what you suggest!!

You guys are amazing! Thank you :)
 
Little_Crow said:
What they said, the only thing to be aware of is that a vlookup will only match against the first column in the array you look up against.
So if you have columns: Weight, Part Name, Price in that order you will need to cut and paste the 'part name' column in front of the others.
(If you can't do this you need to use a combination of INDEX and FIND, and it can get unpleasant).
Click to expand...

All sorted and working perfectly :)

It seems to have worked OK with column number. The commands were:

=VLOOKUP(D12,Parts!A2:C50,2,FALSE)
for grabbing data from column 2

and

=VLOOKUP(D12,Parts!A3:C50,3,FALSE)
from grabbing data for column 3.

Oh and there's not going to be an issue with getting the car part name exact as the cell in worksheet 2 has a dropdown box which is linked to the first column of worksheet 1.
 
Last edited:
You need to click in this bit: Parts!A2:C50 and press f4 so it becomes Parts!$A$2:$C$50.
This makes the cell values absolute. What this means is when you drag it down it stops the next lookup becoming Parts!A3:C51 and will make it stop working as you head down the sheet.
 
I see what you mean, thanks. What happens when I want to add more products to worksheet1, even add products in the middle of the list? Do I just go into the vlookup command in worksheet2 and extend the range manually?
 
Last edited:
If you right click and insert a new row within the range of where the vlookup is looking, it will increase the lookup range by 1 automatically.
 
Exactly that.
Or to reduce the amount of effort when this happens you could use the name manager. Highlight the range of cells and click 'define name', It should default to absolute references and you just need to give it a friendly name.
Then modify your vlookup to use the friendly name rather than a cell range.

If you add data, you can just edit the range in name manager and all your vlookups are updated automagically, without having to go back in and copy the cells down.

And this is why I like people who learn by doing in excel. Learning one function leads to another, to another, to another....
 
Excellent. I shall do that :)

While you're about I have one other question. To give a simple example:

Two variables, X and Y, each in their own cell.

X + Y = 100

Is there a way of entering a choice of X or Y and the other gets calculated? i.e. if I enter 80 in Cell X, 20 automatically shows up in cell Y but if I enter, let's say 45 in cell Y, 55 shows up in cell X?
 
Hamster said:
Excellent. I shall do that :)

While you're about I have one other question. To give a simple example:

Two variables, X and Y, each in their own cell.

X + Y = 100

Is there a way of entering a choice of X or Y and the other gets calculated? i.e. if I enter 80 in Cell X, 20 automatically shows up in cell Y but if I enter, let's say 45 in cell Y, 55 shows up in cell X?
Click to expand...

EDIT: Mis-read what you are asking for.

In your exact example I think you are going to struggle a bit with circular references (as one cell is directly linked to the other) - you can't change the cell directly as you will remove the forumula itself.

I think this is possible by using different input cells to output cells.
 
Last edited:
I thought it may be a bit of a strange one! I'll try and find another solution.

I have another question related to the original problem:

Let's say I have another column in worksheet1 with the stock level. Is there an easy way of altering it based on the amount of a particular car part I've listed in worksheet2?

The most 'complicated' example would be that in worksheet1 I have, let's say 1000l of oil. In worksheet2, I have chosen 'oil' from the dropdown box and listed a quantity of 200l. I want the stock of oil to be reduced by 200ml in worksheet1.

I'm assuming it will be another vlookup but I can't figure out the best way of doing it.
 
Last edited:
With worksheet 2, would you be creating a new row every time you modify the stock level for oil or updating the original entry?
 
Robofish said:
With worksheet 2, would you be creating a new row every time you modify the stock level for oil or updating the original entry?
Click to expand...

Updating the original entry. There will be a single permanent cell with 'quantity remaining' irrespective of what that product is.
 
Last edited:
-edit- sorry I misread.

Let's say each worksheet from worksheet 2 onwards is a new order. Whenever there is an order that contains oil, I enter the quantity of oil on the worksheet for that order.

What I want is for worksheet1 to be updated with remaining oil.

Also it's not just for oil. There will be several different liquids that can be ordered and I want all of their quantities to update in worksheet1 so it maybe another vlookup.
 
Could I possibly steer you down a different path?

If you're going to have a new worksheet for every order you will need to constantly be updating your vlookups and named ranges (Might also want to look into dynamic named ranges). I would recommend having the following three worksheets:

Stock Master - Gives you a general stock overview
Stock Purchased - Keeps a record of stock you have added to your inventory
Stock Sold - Keeps a record of stock you have used/sold

With these sheets, you can then use a SUMPRODUCT formula on your stock master page to have a column of stock purchased, and a column to show stock sold. You can then take one away from the other to give you your remaining stock. The benefit of having all of your sales on one worksheet is that it makes it a lot easier to create reports for monitoring stock trends.

If you would like to trust me your email address I can send you over a quick mock up.
 
You must log in or register to reply here.
Back
Top Bottom