mads
|
| posted on 14/6/10 at 03:14 PM |
|
|
excel help
hi all,
trying to do something for someone at work. I have got two spreadsheets...
Spreadsheet 1: has a pivot table looking at the products made and for whom in May (has both product code and details)
Spreadsheet 2: has a list of product codes and a weighting value associated with each.
I want Excel to look at spreadsheet 1 (column A) and look at the product code and pull the corresponding weighting value in spreadsheet 2 across and
put it in column J of spreadsheet 1 for me.
I could simply just input it manually but there are over 45 products and it'll need to be done for the past 3-4 months so would take forever.
Plus, not all the products are made each month so there is the risk of inputting the wrong weighting value.
Any suggestions on how to do this quickly and efficiently? 
[Edited on 14/6/10 by mads]
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
|
|
sprouts-car
|
| posted on 14/6/10 at 03:19 PM |
|
|
What you want is Vlookup. It looks up values in a list
Example: http://www.automateexcel.com/2004/08/15/excel_formula_vlookup_function_example/
|
|
|
TimC
|
| posted on 14/6/10 at 03:20 PM |
|
|
It sound like a vlookup to me, although I tend to Index and match as it negates the need for things to be sorted - although I understand that the
latest version of Excel doesn't required columns to be sorted...
|
|
|
mads
|
| posted on 14/6/10 at 03:26 PM |
|
|
cheers guys. have updated the original post which makes more sense in what i am trying to achieve.
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
MikeR
|
| posted on 14/6/10 at 03:28 PM |
|
|
what version of excel are you using?
If it can be saved on 2003 or earlier format, email it to me and i'll do it for you.
|
|
|
mads
|
| posted on 14/6/10 at 03:31 PM |
|
|
running 2007 Mike.
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
MikeR
|
| posted on 14/6/10 at 03:55 PM |
|
|
well a vlookup would be....
=vlookup(value to look up so column a, highlight the columns of the table to look up the value, the column number you want to display, false).
By column number you want to display, if you had columns A,B,C,D,E,F and 'A' contains the value that is what you want to match and F
contains the value you want to display you'd enter 6 as 'F' is the sixth value.
False means the computer doesn't find the nearest value - it can if you want find the closest match and report that. I've never used the
feature so always specify false.
The problem you may have if referencing something from within a pivot table. I seem to recall trying it a few weeks ago, my 'simple'
solution was to highlight the pivot table, copy and right click, past special, select values. then do the lookup against the new data you've
just pasted.
|
|
|
loggyboy
|
| posted on 14/6/10 at 03:55 PM |
|
|
Could you be after as simple as this?
2 sheets, 1 called 'sheet1', other 'sheet2'
in a cell sheet 1 type =Sheet2!A1
this will display the value of A1 of sheet2 in the cell of sheet 1 in which you type the above
[Edited on 14/6/10 by loggyboy]
|
|
|
mads
|
| posted on 14/6/10 at 03:59 PM |
|
|
OK, ran the VLOOKUp function and looks like what I needed. Problem is that in some instances it gives the weighting as expected and in some cells it
gives the product description, which is in the column next to the product code. Weird!
I have since moved the workbook 2 into workbook 1 as another sheet
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
mads
|
| posted on 14/6/10 at 04:18 PM |
|
|
the error I get is #REF! which looking on MS website is:
quote:
Col_index_num The column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first
column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is:
- Less than 1, VLOOKUP returns the #VALUE! error value.
- Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.
But I dont understand where the problem lies. Anyone able to give me some pointers?
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
MikeR
|
| posted on 14/6/10 at 04:30 PM |
|
|
daft question - are all your numbers, numbers?
Excel can hold numbers or it can hold text that is a number,
eg, 123 is a number,
"123" is text
You can't do a vlookup using "123" to find 123.
My trick is to add a column at the start of every table and do
=text(field with number,0)
and then next to the column to be looked up do the same, eg,
=text(field with value to lookup,0).
This means excel is using the same format for the lookup values. If you're doing a lot of lookups you'll need to copy and paste values the
columns so excel doesn't have to keep calculating them (on 80k row spreadsheets vlookups the copy and paste saves a lot of time).
|
|
|
MikeR
|
| posted on 14/6/10 at 04:31 PM |
|
|
oh, once you've got the '=text' values - resort your spreadsheet tab with the reference values as some may now be in a different
order. Any out of order won't be found on the vlookup.
|
|
|
mads
|
| posted on 14/6/10 at 04:36 PM |
|
|
quote:
Originally posted by MikeR
daft question - are all your numbers, numbers?
No, the product code is made up of text and numbers. What I dont understand is why it works for some codes but not others.
Mike - you have U2U
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
