Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up in work spreadsheet
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up in work spreadsheet
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 On Sat, 28 Apr 2007 10:24:02 -0700, 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up in work spreadsheet
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up in work spreadsheet
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up in work spreadsheet
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up in work spreadsheet
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" wrote: 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
look up in work spreadsheet
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 On Sun, 29 Apr 2007 09:06:02 -0700, Joel wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If I PDF a spreadsheet the Links don't work there - help | Links and Linking in Excel | |||
spreadsheet to work my wages out.tax and insurance | Excel Worksheet Functions | |||
excel-hrs spreadsheet-work 8:15 - 5:30 (how calculate total hrs?) | Excel Worksheet Functions | |||
Please help on an Excel spreadsheet for work | Excel Discussion (Misc queries) | |||
why does a formula only work when the linked spreadsheet is open | Excel Discussion (Misc queries) |