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

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

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

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

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

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

Gary's Student:
Thanks again for your fast answer - I love this forum - and feel blessed to
have found someone so helpful!

Here's the info in the first 5 lines (my columns run B,C,D,E) (my rows are
15-19) on my working order sheet:
270017 #REF! Carob Coated Peanuts Non Hyd Oil 23.50
201244 12 oz Honey, OG Imp 6.75
270018 10# Carob Coated Raisins Non Hyd Oil 24.50
331604 16 oz. Cinnamon, Ground 2.60
331034 16 oz. Cumin, Ground 5.70

Where you see the #REF! - the formula I have in that field is this:
=VLOOKUP(B15,M1:M184,2)

My data is in columns M-P starting at row 1 - as you can see through 184 -
so far. I need to add hundreds of more products but will do so once I get
the linked order page set up.

Hope that is what you need to help me. Every time I tried to create the
table using the Data, Table command from the drop down menu, I can't figure
out what cell(s) to reference for the input row cell reference and the input
column cell reference. Perhaps that is my only problem is that I don't have
that referenced yet - at least that is what I discerned from the "#REF!"
error code information I did find on the help screen.
Thanks and God Bless!
"Gary''s Student" wrote:

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