Joel

Gord is not doing anything other than posting an alternative.

I believe you are attempting to communicate with "whohasmynameuk"


Thanks, Gord

On Sun, 29 Apr 2007 03:40:00 -0700, Joel wrote:

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


"Joel" wrote:

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


"whohasmynameuk" wrote:

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.