I need to automatically retrieve data relating to a product code upon entry.

You don't have Colour as one of the fields in your database.

You can do this using VLOOKUP - put this formula in B20 of your order form:

=IF(A20="","",VLOOKUP($A20,Sheet1!$A:$F,column(),0))

This can then be copied across and down.

Hope this helps.

Pete

Thanks very much for the reply Pete_UK

I am doing my best to make sense of the formula and your advice, but as previously stated I am very unskilled when it comes to excel as I do not have much prior experience.
What do you mean by I have no colour as one of the fields in my database? And what relevance does it have to the functioning of the formula?

If you would, please explain the parts of the formula and what each one refers to.

Regards,
Kyle

In your order form your have a columns for Material, Colour and Size - Material can be obtained from column C of your database and Size is in column D of your database, but there is nothing to give your the Colour information. In your example table above your have 125mm shown under Colour with a Size of 1, so your example is inconsistent.

I've noticed that you are using merged cells in your order form, so the formula that I gave you will not work. You will have to put this formula in B20:

Formula:

Please Login or Register  to view this content.

and this one in F20:

Formula:

Please Login or Register  to view this content.

G20 would have to be left empty as there is nowhere to get that data from, then you could put this in H20:

Formula:

Please Login or Register  to view this content.

and in I20 you can have this:

Formula:

Please Login or Register  to view this content.

and finally you can put this one in J20:

Formula:

Please Login or Register  to view this content.

Notice that these formulae are very similar - the only thing that changes is the 3rd parameter within the VLOOKUP function, as this relates the column number where you want to get the data from.

VLOOKUP has up to 4 parameters, and allows you to retrieve data from a table. The first parameter is the lookup value - the value that you want to find in the table and in this case is the code that can be found in A20. The second parameter defines where the data can be found, and in this case it is columns A to F on Sheet1. The function works by searching through the left-most column of the table (column A of Sheet1) looking for a matching entry. If a match is found then it returns the corresponding value on the same row of the table in the column given by the third parameter. The final parameter defines the type of match which is looked for - a 0 (or FALSE) indicates an exact match.

Anyway, the formulae that I have given above can then be copied down on your order form to allow other items to be ordered.

Hope this helps.

Pete

@ Kylemarc

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Thank you Cutter, I will keep all of that in mind for future posts. Thanks for a great forum.

New information: The product information and the order form have changed slightly. All my attempts to edit the previously working formula have not yielded the desired results. I now need a new formula to do the following:

The new product information is attached in the image below. (Column 1: Code, Column 2: Description, Column 3: Colour and material, Column 4: Size, Column 5: Quantity **PER PACKET**, Column 6: Price)

NewData.jpg

The new order form is attached below:

NewOrderForm.jpg

As stated in my preceding posts, the customers browse pictures on our website which display the information of each product. What we are trying to create is an order form which does all the work for the customer by bringing all relevant information about products and prices to the order form for them and saves everyone a lot of time and confusion.

I would like a formula that I can paste into each cell within the red square so that when the product code is entered into the code column, all the columns within the boundaries of the red square relating to that product code is automatically found in sheet1 and brought to the correct row.

I hope I have explained this well enough as my excel terms are very limited. Please ask if any clarification is necessary.

Appreciate all of the help!

Regards,
Kyle

Put this formula in C20:

Formula:

Please Login or Register  to view this content.

then copy across into D20:G20. Then copy C20:G20 down to the bottom of your red square.

Hope this helps.

Pete

Pete to the rescue! Works like a charm. THANK YOU PETE_UK.

Hi Pete,

I also need help with something similar. But I have tried to use this as an example but I just cant seem to get the formula correct.
I dont know where im going wrong?? Please help!!

Capture.PNG

Capture 2.PNG

by putting the product code in on the second attachment, the description of goods should automatically appear.

Thank you in Advance

Originally Posted by Pete_UK

In your order form your have a columns for Material, Colour and Size - Material can be obtained from column C of your database and Size is in column D of your database, but there is nothing to give your the Colour information. In your example table above your have 125mm shown under Colour with a Size of 1, so your example is inconsistent.

I've noticed that you are using merged cells in your order form, so the formula that I gave you will not work. You will have to put this formula in B20:

Formula:

Please Login or Register  to view this content.

and this one in F20:

Formula:

Please Login or Register  to view this content.

G20 would have to be left empty as there is nowhere to get that data from, then you could put this in H20:

Formula:

Please Login or Register  to view this content.

and in I20 you can have this:

Formula:

Please Login or Register  to view this content.

and finally you can put this one in J20:

Formula:

Please Login or Register  to view this content.

Notice that these formulae are very similar - the only thing that changes is the 3rd parameter within the VLOOKUP function, as this relates the column number where you want to get the data from.

VLOOKUP has up to 4 parameters, and allows you to retrieve data from a table. The first parameter is the lookup value - the value that you want to find in the table and in this case is the code that can be found in A20. The second parameter defines where the data can be found, and in this case it is columns A to F on Sheet1. The function works by searching through the left-most column of the table (column A of Sheet1) looking for a matching entry. If a match is found then it returns the corresponding value on the same row of the table in the column given by the third parameter. The final parameter defines the type of match which is looked for - a 0 (or FALSE) indicates an exact match.

Anyway, the formulae that I have given above can then be copied down on your order form to allow other items to be ordered.

Hope this helps.

Pete