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.