look up in work spreadsheet

[whohasmynameuk]

Hi guys
Wonder if you can help. I work in a returns section at work and we have to
list all returns on a spreadsheet. There are various items we have to input
but the most important is the material code, description and value. What we
like to do is set up a look up that does these 3 items automatically.

So I would need to manually type in the material code in colonm I the
description would then pop up in J. I would also need the price to pop up in
M but times by column K

I have included an example on the excel 2003 spreadsheet of what we want.

http://www.whalford.pwp.blueyonder.co.uk/Return.xls

Some days we only get a couple of returns but some days we get hundreds so
columns I, M would have to run down the sheet. I would imagine I could just
enter the formula and drag it down.

There is a data sheet were the look up would get the info although because
we are going to use this data sheet and lookup on several similar spreadsheet
we were going to keep the data on a central spreadsheet as new products are
always added. Does this sound possible? If not we would keep the data in each
spreadsheet.

 

[Gord Dibben]

Check out VLOOKUP function.

Better yet.................

See Debra Dalgleish's site for more on VLOOKUP and Data
Validation lists for entering the choices.

http://www.contextures.on.ca/xlFunctions02.html

http://www.contextures.on.ca/xlDataVal01.html

Note the section on using DV lists from another worksheet by naming the list.


Gord Dibben MS Excel MVP

 

[Joel]

The best way to do this is with a worksheet_change function
right click on Return Tab on bottom of worksheet. Select view code.

copy and past code below from Sub to:end sub. the when you type in column I
(column 9) the code will look up the value on the data worksheet and insett
the value in columns J and M.


Sub worksheet_change(ByVal Target As Range)


If Target.Column = 9 Then

If Not IsEmpty(Target) Then
Application.EnableEvents = False
lastrow = Sheets("data").Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 2 To lastrow

If Target = Sheets("data").Cells(RowCount, "A") Then

Cells(Target.Row, "J") = Sheets("data").Cells(RowCount, "C")
Cells(Target.Row, "M") = _
Sheets("data").Cells(RowCount, "B") * _
Cells(Target.Row, "K")
Application.EnableEvents = True
Exit Sub
End If
Next RowCount
MsgBox ("Did not Find " + CStr(Target))
End If
End If
Application.EnableEvents = True
End Sub

 

[Joel]

Gord: I did something very similar to this last month on a spreadsheet where
I had to enter over 6000 entries. I know what you are doing. I added two
improvvements from yesterdays code. first I put in a formula in column M for
the total. this way if you change the cost or the quantity the total will
also change

Second I added a feature so you can copy cells and the function will still
work. when I did my worksheet I found I was doing some copy and pasting and
only the first cell of the group was changing. So I made a little fix. The
new code has this fix.

If you used my code from yesterday, then simply put the new code in place of
the old code. Then highlight column I and do a copy and past of I in the
same column. This will run my macro fro every cell in column I and will
change column M to a formula


Sub worksheet_change(ByVal Target As Range)

For Each cell In Target
If cell.Column = 9 Then

If Not IsEmpty(cell) Then
Application.EnableEvents = False
lastrow = Sheets("data").Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 2 To lastrow

If cell = Sheets("data").Cells(RowCount, "A") Then

Cells(cell.Row, "J") = Sheets("data").Cells(RowCount, "C")

Cells(cell.Row, "M").Formula = "=" + _
"data!B" + CStr(RowCount) + "*" + _
"K" + CStr(cell.Row)
Application.EnableEvents = True
Exit Sub
End If
Next RowCount
MsgBox ("Did not Find " + CStr(cell))
End If
End If
Next cell
Application.EnableEvents = True
End Sub

 

[Gord Dibben]

Joel

Gord is not doing anything other than posting an alternative.

I believe you are attempting to communicate with "whohasmynameuk"


Thanks, Gord

 

[Joel]

You are right.
I like the worksheet changge method because you can have a msgbox tell you
when you type an illegal value. I have a project coming up that has almost
24,000 line cell that I have to enter by hand. It is an analysis which can't
be automated. The entry is a long string which varies and can contain up to
20 characters (400 different strings). My plan is to number the strings from
1 - 400. Then simplly enter the number and have Excel lookup the strings to
save time and to eliminate typo errrors.

 

[Gord Dibben]

Joel

Change event is one way to go and may be easiest but not all posters are allowed
to enable macros so VLOOKUP and DV dropdowns can assist that brand of poster.


Gord