pricebook help
i am working on a pricebook where i will use a dropdown list to select an
item description...the adjacent 3 or 4 columns will fill in with the selected item's information(such as price and code) when the item is selected. How exactly would I do this? Also, after i select one item out of that list, i need to be able to select more, so is there a way for me to make everything skip a line after an item is selected? Thank you for any help you may be able to give. |
first of all, you need to create the list of prices. You need to have one
column with a unique ID so that you can find an article by ID. Then on the column where you want to lookup a price, use the Validation function under the Data menu. Choose value from list, and select all your IDs. That will add a combo button to the cells you have selected. After that you need to create a lookup function for the product description, price, etc. On the cell you want the name to appear, use the following formula: =Vlookup(A1,Sheet2!A1:E12,2,FALSE) Here, A1 would be the cell where you have the ID for the row you have selected. Sheet2!A1:E12 would be the range in which all your data exists(that is the prices and descriptions and IDs). The IDs column must be the first column. The number "2" is the number of column where the description of your price is. You can change it to 3 for your price or whatever data is in the third row. The FALSE argument specifies that you need the ID to be exactly the same. If you use TRUE or ommit it you will get the closests ID and you need to sort your IDs in ascending order. I really hope this helps, and if it´s not clear enough please tell me. If it does help even a little bit, I would appreciate it if you voted for my answer. Thanks! G.Morales "BLW" wrote: i am working on a pricebook where i will use a dropdown list to select an item description...the adjacent 3 or 4 columns will fill in with the selected item's information(such as price and code) when the item is selected. How exactly would I do this? Also, after i select one item out of that list, i need to be able to select more, so is there a way for me to make everything skip a line after an item is selected? Thank you for any help you may be able to give. |
ok, i think that may help somewhat...it will be a while before i can really
get going on this due to the large amount of information i have...i will try to get back with you for more help in the next few hours or days. Thanks a lot. "Mexage" wrote: first of all, you need to create the list of prices. You need to have one column with a unique ID so that you can find an article by ID. Then on the column where you want to lookup a price, use the Validation function under the Data menu. Choose value from list, and select all your IDs. That will add a combo button to the cells you have selected. After that you need to create a lookup function for the product description, price, etc. On the cell you want the name to appear, use the following formula: =Vlookup(A1,Sheet2!A1:E12,2,FALSE) Here, A1 would be the cell where you have the ID for the row you have selected. Sheet2!A1:E12 would be the range in which all your data exists(that is the prices and descriptions and IDs). The IDs column must be the first column. The number "2" is the number of column where the description of your price is. You can change it to 3 for your price or whatever data is in the third row. The FALSE argument specifies that you need the ID to be exactly the same. If you use TRUE or ommit it you will get the closests ID and you need to sort your IDs in ascending order. I really hope this helps, and if it´s not clear enough please tell me. If it does help even a little bit, I would appreciate it if you voted for my answer. Thanks! G.Morales "BLW" wrote: i am working on a pricebook where i will use a dropdown list to select an item description...the adjacent 3 or 4 columns will fill in with the selected item's information(such as price and code) when the item is selected. How exactly would I do this? Also, after i select one item out of that list, i need to be able to select more, so is there a way for me to make everything skip a line after an item is selected? Thank you for any help you may be able to give. |
Could you help me any concerning the skipping line procedure??
Thanks "BLW" wrote: ok, i think that may help somewhat...it will be a while before i can really get going on this due to the large amount of information i have...i will try to get back with you for more help in the next few hours or days. Thanks a lot. "Mexage" wrote: first of all, you need to create the list of prices. You need to have one column with a unique ID so that you can find an article by ID. Then on the column where you want to lookup a price, use the Validation function under the Data menu. Choose value from list, and select all your IDs. That will add a combo button to the cells you have selected. After that you need to create a lookup function for the product description, price, etc. On the cell you want the name to appear, use the following formula: =Vlookup(A1,Sheet2!A1:E12,2,FALSE) Here, A1 would be the cell where you have the ID for the row you have selected. Sheet2!A1:E12 would be the range in which all your data exists(that is the prices and descriptions and IDs). The IDs column must be the first column. The number "2" is the number of column where the description of your price is. You can change it to 3 for your price or whatever data is in the third row. The FALSE argument specifies that you need the ID to be exactly the same. If you use TRUE or ommit it you will get the closests ID and you need to sort your IDs in ascending order. I really hope this helps, and if it´s not clear enough please tell me. If it does help even a little bit, I would appreciate it if you voted for my answer. Thanks! G.Morales "BLW" wrote: i am working on a pricebook where i will use a dropdown list to select an item description...the adjacent 3 or 4 columns will fill in with the selected item's information(such as price and code) when the item is selected. How exactly would I do this? Also, after i select one item out of that list, i need to be able to select more, so is there a way for me to make everything skip a line after an item is selected? Thank you for any help you may be able to give. |
Well, I don't know if I understood completely, but let me try...
Is what you want is something like the following?: A B C 1 ID Desc Price 2 0001 Peanuts $5.00 3 4 After you select 0001 on A2 A B C 1 ID Desc Price 2 0015 Coconut $10.00 3 0001 Peanut $ 5.00 4 5 If you want rows to pile up, you will need a macro... but why don't you place all formulas in a table and go from top to bottom? I hope I understood, if I didn't please try showing an example as I did above... G.Morales "BLW" wrote: Could you help me any concerning the skipping line procedure?? Thanks "BLW" wrote: ok, i think that may help somewhat...it will be a while before i can really get going on this due to the large amount of information i have...i will try to get back with you for more help in the next few hours or days. Thanks a lot. "Mexage" wrote: first of all, you need to create the list of prices. You need to have one column with a unique ID so that you can find an article by ID. Then on the column where you want to lookup a price, use the Validation function under the Data menu. Choose value from list, and select all your IDs. That will add a combo button to the cells you have selected. After that you need to create a lookup function for the product description, price, etc. On the cell you want the name to appear, use the following formula: =Vlookup(A1,Sheet2!A1:E12,2,FALSE) Here, A1 would be the cell where you have the ID for the row you have selected. Sheet2!A1:E12 would be the range in which all your data exists(that is the prices and descriptions and IDs). The IDs column must be the first column. The number "2" is the number of column where the description of your price is. You can change it to 3 for your price or whatever data is in the third row. The FALSE argument specifies that you need the ID to be exactly the same. If you use TRUE or ommit it you will get the closests ID and you need to sort your IDs in ascending order. I really hope this helps, and if it´s not clear enough please tell me. If it does help even a little bit, I would appreciate it if you voted for my answer. Thanks! G.Morales "BLW" wrote: i am working on a pricebook where i will use a dropdown list to select an item description...the adjacent 3 or 4 columns will fill in with the selected item's information(such as price and code) when the item is selected. How exactly would I do this? Also, after i select one item out of that list, i need to be able to select more, so is there a way for me to make everything skip a line after an item is selected? Thank you for any help you may be able to give. |
After I select the first item, i need for the dropdown list to drop to the
next line so i can do it all over again...so that i may have a hundred items that i may choose from, but i don't have to get all of them and only the ones that i want show up... so yes, i would say that i need them to pile up...i think we may be a little offkey here because i don't think i expressed that i have to use a dropdown list for this. However, other than that, the example you just provided is exactly what i need. All i need now is to know how to make everything drop down one line, including the list...if in fact i need to use a macro, could you help me out a little there--I know how to get into the editor, but i have never used them and am having trouble getting started on everything....Thank you "Mexage" wrote: Well, I don't know if I understood completely, but let me try... Is what you want is something like the following?: A B C 1 ID Desc Price 2 0001 Peanuts $5.00 3 4 After you select 0001 on A2 A B C 1 ID Desc Price 2 0015 Coconut $10.00 3 0001 Peanut $ 5.00 4 5 If you want rows to pile up, you will need a macro... but why don't you place all formulas in a table and go from top to bottom? I hope I understood, if I didn't please try showing an example as I did above... G.Morales "BLW" wrote: Could you help me any concerning the skipping line procedure?? Thanks "BLW" wrote: ok, i think that may help somewhat...it will be a while before i can really get going on this due to the large amount of information i have...i will try to get back with you for more help in the next few hours or days. Thanks a lot. "Mexage" wrote: first of all, you need to create the list of prices. You need to have one column with a unique ID so that you can find an article by ID. Then on the column where you want to lookup a price, use the Validation function under the Data menu. Choose value from list, and select all your IDs. That will add a combo button to the cells you have selected. After that you need to create a lookup function for the product description, price, etc. On the cell you want the name to appear, use the following formula: =Vlookup(A1,Sheet2!A1:E12,2,FALSE) Here, A1 would be the cell where you have the ID for the row you have selected. Sheet2!A1:E12 would be the range in which all your data exists(that is the prices and descriptions and IDs). The IDs column must be the first column. The number "2" is the number of column where the description of your price is. You can change it to 3 for your price or whatever data is in the third row. The FALSE argument specifies that you need the ID to be exactly the same. If you use TRUE or ommit it you will get the closests ID and you need to sort your IDs in ascending order. I really hope this helps, and if it´s not clear enough please tell me. If it does help even a little bit, I would appreciate it if you voted for my answer. Thanks! G.Morales "BLW" wrote: i am working on a pricebook where i will use a dropdown list to select an item description...the adjacent 3 or 4 columns will fill in with the selected item's information(such as price and code) when the item is selected. How exactly would I do this? Also, after i select one item out of that list, i need to be able to select more, so is there a way for me to make everything skip a line after an item is selected? Thank you for any help you may be able to give. |
Well, ok see if this works:
Enter the editor, then double click on the sheet where you want this functionality (for example sheet 3). If you can't see sheets, click on View, then Project explorer. After you double click on the sheet, type in (or copy paste) the following in the code window: ' This will happen every time you change the sheet you double clicked Private Sub Worksheet_Change(ByVal Target As Range) ' If we are modifying B1 If Target.Address(rowabsolute:=False, columnabsolute:=False) = "B1" Then ' Then insert a row below B1 Range("B1").EntireRow.Insert shift:=xlShiftDown ' Return to B1 Range("B1").Select End If End Sub afterwards, go to the excel sheet, and type something in B1... Is that what you needed? G.Morales "BLW" wrote: After I select the first item, i need for the dropdown list to drop to the next line so i can do it all over again...so that i may have a hundred items that i may choose from, but i don't have to get all of them and only the ones that i want show up... so yes, i would say that i need them to pile up...i think we may be a little offkey here because i don't think i expressed that i have to use a dropdown list for this. However, other than that, the example you just provided is exactly what i need. All i need now is to know how to make everything drop down one line, including the list...if in fact i need to use a macro, could you help me out a little there--I know how to get into the editor, but i have never used them and am having trouble getting started on everything....Thank you "Mexage" wrote: Well, I don't know if I understood completely, but let me try... Is what you want is something like the following?: A B C 1 ID Desc Price 2 0001 Peanuts $5.00 3 4 After you select 0001 on A2 A B C 1 ID Desc Price 2 0015 Coconut $10.00 3 0001 Peanut $ 5.00 4 5 If you want rows to pile up, you will need a macro... but why don't you place all formulas in a table and go from top to bottom? I hope I understood, if I didn't please try showing an example as I did above... G.Morales "BLW" wrote: Could you help me any concerning the skipping line procedure?? Thanks "BLW" wrote: ok, i think that may help somewhat...it will be a while before i can really get going on this due to the large amount of information i have...i will try to get back with you for more help in the next few hours or days. Thanks a lot. "Mexage" wrote: first of all, you need to create the list of prices. You need to have one column with a unique ID so that you can find an article by ID. Then on the column where you want to lookup a price, use the Validation function under the Data menu. Choose value from list, and select all your IDs. That will add a combo button to the cells you have selected. After that you need to create a lookup function for the product description, price, etc. On the cell you want the name to appear, use the following formula: =Vlookup(A1,Sheet2!A1:E12,2,FALSE) Here, A1 would be the cell where you have the ID for the row you have selected. Sheet2!A1:E12 would be the range in which all your data exists(that is the prices and descriptions and IDs). The IDs column must be the first column. The number "2" is the number of column where the description of your price is. You can change it to 3 for your price or whatever data is in the third row. The FALSE argument specifies that you need the ID to be exactly the same. If you use TRUE or ommit it you will get the closests ID and you need to sort your IDs in ascending order. I really hope this helps, and if it´s not clear enough please tell me. If it does help even a little bit, I would appreciate it if you voted for my answer. Thanks! G.Morales "BLW" wrote: i am working on a pricebook where i will use a dropdown list to select an item description...the adjacent 3 or 4 columns will fill in with the selected item's information(such as price and code) when the item is selected. How exactly would I do this? Also, after i select one item out of that list, i need to be able to select more, so is there a way for me to make everything skip a line after an item is selected? Thank you for any help you may be able to give. |
when i did tried this, it made the entire thing skip down one line and then
when i changed the selection in the box, it was simply writing over itself(in other words, it wasn't piling up)...what i want is to make the information go down one line, while the list stays in its original place so that i may select something else. Thank you "Mexage" wrote: Well, ok see if this works: Enter the editor, then double click on the sheet where you want this functionality (for example sheet 3). If you can't see sheets, click on View, then Project explorer. After you double click on the sheet, type in (or copy paste) the following in the code window: ' This will happen every time you change the sheet you double clicked Private Sub Worksheet_Change(ByVal Target As Range) ' If we are modifying B1 If Target.Address(rowabsolute:=False, columnabsolute:=False) = "B1" Then ' Then insert a row below B1 Range("B1").EntireRow.Insert shift:=xlShiftDown ' Return to B1 Range("B1").Select End If End Sub afterwards, go to the excel sheet, and type something in B1... Is that what you needed? G.Morales "BLW" wrote: After I select the first item, i need for the dropdown list to drop to the next line so i can do it all over again...so that i may have a hundred items that i may choose from, but i don't have to get all of them and only the ones that i want show up... so yes, i would say that i need them to pile up...i think we may be a little offkey here because i don't think i expressed that i have to use a dropdown list for this. However, other than that, the example you just provided is exactly what i need. All i need now is to know how to make everything drop down one line, including the list...if in fact i need to use a macro, could you help me out a little there--I know how to get into the editor, but i have never used them and am having trouble getting started on everything....Thank you "Mexage" wrote: Well, I don't know if I understood completely, but let me try... Is what you want is something like the following?: A B C 1 ID Desc Price 2 0001 Peanuts $5.00 3 4 After you select 0001 on A2 A B C 1 ID Desc Price 2 0015 Coconut $10.00 3 0001 Peanut $ 5.00 4 5 If you want rows to pile up, you will need a macro... but why don't you place all formulas in a table and go from top to bottom? I hope I understood, if I didn't please try showing an example as I did above... G.Morales "BLW" wrote: Could you help me any concerning the skipping line procedure?? Thanks "BLW" wrote: ok, i think that may help somewhat...it will be a while before i can really get going on this due to the large amount of information i have...i will try to get back with you for more help in the next few hours or days. Thanks a lot. "Mexage" wrote: first of all, you need to create the list of prices. You need to have one column with a unique ID so that you can find an article by ID. Then on the column where you want to lookup a price, use the Validation function under the Data menu. Choose value from list, and select all your IDs. That will add a combo button to the cells you have selected. After that you need to create a lookup function for the product description, price, etc. On the cell you want the name to appear, use the following formula: =Vlookup(A1,Sheet2!A1:E12,2,FALSE) Here, A1 would be the cell where you have the ID for the row you have selected. Sheet2!A1:E12 would be the range in which all your data exists(that is the prices and descriptions and IDs). The IDs column must be the first column. The number "2" is the number of column where the description of your price is. You can change it to 3 for your price or whatever data is in the third row. The FALSE argument specifies that you need the ID to be exactly the same. If you use TRUE or ommit it you will get the closests ID and you need to sort your IDs in ascending order. I really hope this helps, and if it´s not clear enough please tell me. If it does help even a little bit, I would appreciate it if you voted for my answer. Thanks! G.Morales "BLW" wrote: i am working on a pricebook where i will use a dropdown list to select an item description...the adjacent 3 or 4 columns will fill in with the selected item's information(such as price and code) when the item is selected. How exactly would I do this? Also, after i select one item out of that list, i need to be able to select more, so is there a way for me to make everything skip a line after an item is selected? Thank you for any help you may be able to give. |
All times are GMT +1. The time now is 03:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com