Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use MS products to set up a linked product order form?
Not sure if I have the correct group here, or even if I am using the proper
language to explain what I want to do. I need to use Excel (or Access or Works) to create a linkable product order form. This needs to be able to go to the database I create, search for the item # I input and then return to the next 3 columns in the row I am in the values that go along with that item # in the database. (ie: 170113 would be the item # and the next 3 columns would say 12 oz. Cranberry Juice Concentrate, OG 3.75 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use MS products to set up a linked product order form?
Use Excel.
Build a table using columns E thru H. So column E would contain the item # and F and G and H the other three pieces of data. To use the table, enter the desired item # in cell A1 and the following formulas in B1 thru D1: =VLOOKUP(A1,E1:H100,2) =VLOOKUP(A1,E1:H100,3) =VLOOKUP(A1,E1:H100,4) These will find and display the data from the correct row Adjust the 100 to suit your needs -- Gary''s Student - gsnu200771 "goalonggirl" wrote: Not sure if I have the correct group here, or even if I am using the proper language to explain what I want to do. I need to use Excel (or Access or Works) to create a linkable product order form. This needs to be able to go to the database I create, search for the item # I input and then return to the next 3 columns in the row I am in the values that go along with that item # in the database. (ie: 170113 would be the item # and the next 3 columns would say 12 oz. Cranberry Juice Concentrate, OG 3.75 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use MS products to set up a linked product order form
Gary's Student - Thanks for that info. one thing I forgot to mention and not
sure if it is covered by your wonderful solution - perhaps you can tell me - my product info won't always be in the same row. For example if I am entering orders from our buying group into the order form - one person will order a product and another person might order the same product, but it will be 30 lines down. Will this option allow for the query to search vertically in the column of the table for the matching #, and match the data in the other 3 columns similarly? Thanks much and God Bless - its so nice to get information so fast! "Gary''s Student" wrote: Use Excel. Build a table using columns E thru H. So column E would contain the item # and F and G and H the other three pieces of data. To use the table, enter the desired item # in cell A1 and the following formulas in B1 thru D1: =VLOOKUP(A1,E1:H100,2) =VLOOKUP(A1,E1:H100,3) =VLOOKUP(A1,E1:H100,4) These will find and display the data from the correct row Adjust the 100 to suit your needs -- Gary''s Student - gsnu200771 "goalonggirl" wrote: Not sure if I have the correct group here, or even if I am using the proper language to explain what I want to do. I need to use Excel (or Access or Works) to create a linkable product order form. This needs to be able to go to the database I create, search for the item # I input and then return to the next 3 columns in the row I am in the values that go along with that item # in the database. (ie: 170113 would be the item # and the next 3 columns would say 12 oz. Cranberry Juice Concentrate, OG 3.75 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use MS products to set up a linked product order form
My suggestion will work if the Item # appears only once in column E. That
specific row is found by VLOOKUP and the associated data is displayed. If the item # appears several times in column E, then a different technique could be used: AutoFilter Using AutoFilter, ALL rows matching the item # in column E will be displayed. -- Gary''s Student - gsnu200771 "goalonggirl" wrote: Gary's Student - Thanks for that info. one thing I forgot to mention and not sure if it is covered by your wonderful solution - perhaps you can tell me - my product info won't always be in the same row. For example if I am entering orders from our buying group into the order form - one person will order a product and another person might order the same product, but it will be 30 lines down. Will this option allow for the query to search vertically in the column of the table for the matching #, and match the data in the other 3 columns similarly? Thanks much and God Bless - its so nice to get information so fast! "Gary''s Student" wrote: Use Excel. Build a table using columns E thru H. So column E would contain the item # and F and G and H the other three pieces of data. To use the table, enter the desired item # in cell A1 and the following formulas in B1 thru D1: =VLOOKUP(A1,E1:H100,2) =VLOOKUP(A1,E1:H100,3) =VLOOKUP(A1,E1:H100,4) These will find and display the data from the correct row Adjust the 100 to suit your needs -- Gary''s Student - gsnu200771 "goalonggirl" wrote: Not sure if I have the correct group here, or even if I am using the proper language to explain what I want to do. I need to use Excel (or Access or Works) to create a linkable product order form. This needs to be able to go to the database I create, search for the item # I input and then return to the next 3 columns in the row I am in the values that go along with that item # in the database. (ie: 170113 would be the item # and the next 3 columns would say 12 oz. Cranberry Juice Concentrate, OG 3.75 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use MS products to set up a linked product order form
Gary's Student,
Thank you again for such a fast answer. I find that the original solution will probably work for my needs, my only problem now is I can't quite figure how to create a table in Excel? I've entered my data into the cells in 4 consecutive columns, and when I went and typed in the formula you gave - adjusting the names as needed for the columns/rows needed, I get an error of REF#. Upon further investigation, I find I must actually create the table - but when I do try that - I get an error of cell input reference invalid. I've searched the online database and tutorials as well as general help for my solution - and there just isn't anything out there that tells you WHAT is invalid about the cell reference. I've tried every concievable combination and hope you can once again save the day for me? "Gary''s Student" wrote: My suggestion will work if the Item # appears only once in column E. That specific row is found by VLOOKUP and the associated data is displayed. If the item # appears several times in column E, then a different technique could be used: AutoFilter Using AutoFilter, ALL rows matching the item # in column E will be displayed. -- Gary''s Student - gsnu200771 "goalonggirl" wrote: Gary's Student - Thanks for that info. one thing I forgot to mention and not sure if it is covered by your wonderful solution - perhaps you can tell me - my product info won't always be in the same row. For example if I am entering orders from our buying group into the order form - one person will order a product and another person might order the same product, but it will be 30 lines down. Will this option allow for the query to search vertically in the column of the table for the matching #, and match the data in the other 3 columns similarly? Thanks much and God Bless - its so nice to get information so fast! "Gary''s Student" wrote: Use Excel. Build a table using columns E thru H. So column E would contain the item # and F and G and H the other three pieces of data. To use the table, enter the desired item # in cell A1 and the following formulas in B1 thru D1: =VLOOKUP(A1,E1:H100,2) =VLOOKUP(A1,E1:H100,3) =VLOOKUP(A1,E1:H100,4) These will find and display the data from the correct row Adjust the 100 to suit your needs -- Gary''s Student - gsnu200771 "goalonggirl" wrote: Not sure if I have the correct group here, or even if I am using the proper language to explain what I want to do. I need to use Excel (or Access or Works) to create a linkable product order form. This needs to be able to go to the database I create, search for the item # I input and then return to the next 3 columns in the row I am in the values that go along with that item # in the database. (ie: 170113 would be the item # and the next 3 columns would say 12 oz. Cranberry Juice Concentrate, OG 3.75 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use MS products to set up a linked product order form
To get this to work, we need some specifics. I need you to:
1. post the data in the first four or five rows, that is E1 thru H5 2. set-up the "find value" to find the third row. 3. if you have modified the equations I gave, post them as well Here is an example: In E1 thru H5 we put: 170113 soap OG 3.75 170114 amonnia AQ 1.56 170115 roses PL 10.45 170116 hammer HW 19.95 170117 toothpaste DR 3.54 In A1 we put: 170115 In B1 thru D1 we put: =VLOOKUP(A1,E1:H100,2) =VLOOKUP(A1,E1:H100,3) =VLOOKUP(A1,E1:H100,4) and they display: roses PL 10.45 -- Gary''s Student - gsnu200772 "goalonggirl" wrote: Gary's Student, Thank you again for such a fast answer. I find that the original solution will probably work for my needs, my only problem now is I can't quite figure how to create a table in Excel? I've entered my data into the cells in 4 consecutive columns, and when I went and typed in the formula you gave - adjusting the names as needed for the columns/rows needed, I get an error of REF#. Upon further investigation, I find I must actually create the table - but when I do try that - I get an error of cell input reference invalid. I've searched the online database and tutorials as well as general help for my solution - and there just isn't anything out there that tells you WHAT is invalid about the cell reference. I've tried every concievable combination and hope you can once again save the day for me? "Gary''s Student" wrote: My suggestion will work if the Item # appears only once in column E. That specific row is found by VLOOKUP and the associated data is displayed. If the item # appears several times in column E, then a different technique could be used: AutoFilter Using AutoFilter, ALL rows matching the item # in column E will be displayed. -- Gary''s Student - gsnu200771 "goalonggirl" wrote: Gary's Student - Thanks for that info. one thing I forgot to mention and not sure if it is covered by your wonderful solution - perhaps you can tell me - my product info won't always be in the same row. For example if I am entering orders from our buying group into the order form - one person will order a product and another person might order the same product, but it will be 30 lines down. Will this option allow for the query to search vertically in the column of the table for the matching #, and match the data in the other 3 columns similarly? Thanks much and God Bless - its so nice to get information so fast! "Gary''s Student" wrote: Use Excel. Build a table using columns E thru H. So column E would contain the item # and F and G and H the other three pieces of data. To use the table, enter the desired item # in cell A1 and the following formulas in B1 thru D1: =VLOOKUP(A1,E1:H100,2) =VLOOKUP(A1,E1:H100,3) =VLOOKUP(A1,E1:H100,4) These will find and display the data from the correct row Adjust the 100 to suit your needs -- Gary''s Student - gsnu200771 "goalonggirl" wrote: Not sure if I have the correct group here, or even if I am using the proper language to explain what I want to do. I need to use Excel (or Access or Works) to create a linkable product order form. This needs to be able to go to the database I create, search for the item # I input and then return to the next 3 columns in the row I am in the values that go along with that item # in the database. (ie: 170113 would be the item # and the next 3 columns would say 12 oz. Cranberry Juice Concentrate, OG 3.75 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Order form linked to inventory | Excel Discussion (Misc queries) | |||
Product Selection order form | Excel Worksheet Functions | |||
select products to order-in other sheet total num of that product reduced | New Users to Excel | |||
Sort data into order of product | Excel Discussion (Misc queries) | |||
Sort data into order of product | Excel Worksheet Functions |