Formula for extracting data, lookup value in noncontinuous layout

[GAURAVVOHRA]

Hello, I'm trying to create workbook for tracking orders & bom for ordered items. My data looks as shown below, assuming data in column no. A,B,C,D and starts from Row 2.

format query.gif



Query is, if item code for a new order is entered in column A & it is already present in rows above the point of entry, then all data related to that item code is extracted & placed alongside the new entry. If not then we can manually enter the same. Kindly suggest a formula, assuming data set can finally increase to approx. 4000 rows.

[Pepe Le Mokko]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[GAURAVVOHRA]

Refer the attachment. Related queries are also mentioned in the attached file.

[kvsrinivasamurthy]

Code for worksheet event

Please Login or Register  to view this content.

[GAURAVVOHRA]

Thanks for the reply.
Can we do it with a formula , as I'm inexperienced with VBA.

[kvsrinivasamurthy]

It is not possible with formula.When there is new entry you have to put new data.

[Pepe Le Mokko]

@kvsrin

Are you positive about that? If so, I will move it to the VAB forum

[Bo_Ry]

Please try at

B12:C12

Please Login or Register  to view this content.

D12 copy across

Please Login or Register  to view this content.

Copy B12:I12 Down

[GAURAVVOHRA]

Thanks, Bo-Ry.
It seems to work well, can you explain what does look up value "zz" imply, since there is no entry in column A by this name.
Secondly any reply on query 2, viz. how to auto calculate data in column K( quantity of parts) for an item based on qty. given in column J(qty. of item).