Any excel experts out there?

Hamster · 31 Jul 2013 at 08:07

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?

Hamster · 31 Jul 2013 at 08:49

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

Hamster · 31 Jul 2013 at 08:59

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).

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.

Hamster · 31 Jul 2013 at 09:38

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?

Hamster · 31 Jul 2013 at 09:54

Great stuff Mirmo, thanks

Hamster · 31 Jul 2013 at 10:17

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 · 31 Jul 2013 at 20:57

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.

Hamster · 1 Aug 2013 at 17:57

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?

Updating the original entry. There will be a single permanent cell with 'quantity remaining' irrespective of what that product is.

Hamster · 2 Aug 2013 at 07:03

-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.