pick data from first sheet & update other sheets.

**johncena** · 04-22-2010, 02:41 AM

dear friends here i have attached my document.first sheet is my data base.second & thread sheets are my packing reports.when enter to data second & thread sheet column "F" according to that data Column "A" to "E" rows need to fill.that data need to pick from first sheet.i have try vlookup formulas.but it's not giving correct answer for me.pls any can make VB code for this.thanks a lot.

**JBeaucaire** · 04-22-2010, 03:03 AM

VLOOKUP() requires that the search value be found in the first column of the data range. Since your data range is A:M, then that would be column A. Obviously your layout doesn't support that, so you can't use VLOOKUP unless you move the lot # to the first column.

Do you want to do that? If you do your VLOOKUP() will work then.

If not, you'll need to use INDEX/MATCH instead. In B5, for instance:
=INDEX('Order History'!H:H, MATCH($F5, 'Order History'!$M:$M, 0))

(you would need to change F5 to text, or the values in OrderHistory!M:M to numbers)

**antoka05** · 04-22-2010, 03:23 AM

See attached file, I hope it's what you need.
I added this macro on sheets Day and Night:

Please Login or Register  to view this content.

and this macro on Module1

Please Login or Register  to view this content.

Regards,
Antonio

**johncena** · 04-22-2010, 03:37 AM

thanks for your help JB.antoka05 VB code work very well.i need small modification.some time that lot numbers cumming like this.
ex- first sheet column "M" (LOT#) - 125467A-C
that mean 3 lots are there (A,B & C)
that packing sheets it's entering like this.
125467A
125467B
125467C
in this case antoka05 VB code not working.in this case we have to match only first 6 characters.if we match whole cell values it's not work.pls make this change.thanks a lot....

**antoka05** · 04-22-2010, 03:44 AM

I modified code matching only first 6 characters.
If you need to verify that the final letters of lot# in first sheet matching with the letters entered on other sheets please tell me.
Regards,
Antonio

**johncena** · 04-22-2010, 03:57 AM

WOW thanks a lot antoka05.it's 200% working.now my problem is solved.this part not clear for me.

Please Login or Register  to view this content.

thanks a lot again.REP+

**antoka05** · 04-22-2010, 04:40 AM

What I wanted to tell it''s:
for example, if you have code 123456A-C in first sheet and you want it only matches with codes from 123456A to 123456C and not, for example, with 123456D or 123456F etc. I need to modify the code to verify also the comparison of letters and not only match with first 6 characters.
Regards,
Antonio