Automatically pulling cells from one sheet to the next when they match columns

[sfdrew04]

So I am trying to automate my produce inventory for a hotel and I can't seem to get the code right to pull costs from one sheet to another when a certain cell matches (the item #).

An example of the sheets I have are below. I was able to automate the C-D column cells, however with the E-F column there is more products available to choose from and it is a continuous changing list of availability. What I need to do is pull the cell in Column D from sheet 2 and replace a cell in Column E on sheet 1, only when corresponding rows match (Row F on sheet 1 with Column A on sheet 2). So when the item number matches from Sheet 2 to Sheet 1, it automatically changes the price on sheet 1 to correspond with the item number's price on Sheet 2.

Here is an example of what I am working with, let me know if you have any ideas or if it is going to be an ultra-lengthy coding process that I wont be able to attempt. Thank you for all your help. It has been driving me nuts for a week trying different codes and ideas.


Sheet 1
[TABLE="width: 535"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item[/TD]
[TD][/TD]
[TD]Shamrock[/TD]
[TD]Shamrock Price[/TD]
[TD]Freshpoint Price[/TD]
[TD]FreshPT[/TD]
[TD]Pack[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]DATE - - - - - - ->[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Potato Fingerling Bulk[/TD]
[TD]F[/TD]
[TD]3111541[/TD]
[TD]63.28[/TD]
[TD]44.85[/TD]
[TD]608705[/TD]
[TD]50#[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Potato Idaho[/TD]
[TD]S[/TD]
[TD]1182321[/TD]
[TD]20.8[/TD]
[TD]20.96[/TD]
[TD]110186[/TD]
[TD]60 ct.[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Potato Idaho[/TD]
[TD]S[/TD]
[TD]1169701[/TD]
[TD]12.83[/TD]
[TD]13.46[/TD]
[TD]104911[/TD]
[TD]100 ct[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Potato Purple[/TD]
[TD]S[/TD]
[TD]1214341[/TD]
[TD]54.8[/TD]
[TD]11.24(10#)[/TD]
[TD]940779[/TD]
[TD]50#[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Potato Red "B"s[/TD]
[TD]F[/TD]
[TD]4685871[/TD]
[TD]25.1[/TD]
[TD]25.15[/TD]
[TD]174749[/TD]
[TD]50 #[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Potato Yam Jumbo[/TD]
[TD]F[/TD]
[TD]1273811[/TD]
[TD]21.1[/TD]
[TD]20.87[/TD]
[TD]160613[/TD]
[TD]40 #[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Potato Yukon "Utility A"[/TD]
[TD]S[/TD]
[TD]1854041[/TD]
[TD]14.6[/TD]
[TD]17.65[/TD]
[TD]127532[/TD]
[TD]50 #[/TD]
[/TR]
</tbody>[/TABLE]



Sheet 2

[TABLE="width: 531"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD] Number[/TD]
[TD] Item Description[/TD]
[TD]Size[/TD]
[TD] Price[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: right"]142246[/TD]
[TD] CABBAGE GREEN SACK LG[/TD]
[TD]45-50#[/TD]
[TD="align: right"]18.08[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]833455[/TD]
[TD] POTATO FINGERLING *COLORADO*[/TD]
[TD]50#[/TD]
[TD="align: right"]44.87[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]116729[/TD]
[TD] GREENS COLLARD[/TD]
[TD]24CT[/TD]
[TD="align: right"]31.93[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]134328[/TD]
[TD] CHARD SWISS RED[/TD]
[TD]12CT[/TD]
[TD="align: right"]27.51[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]787977[/TD]
[TD] PEA ENGLISH BUSHEL *SP*[/TD]
[TD]CASE[/TD]
[TD="align: right"]49.03[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]158022[/TD]
[TD] PEPPER BELL YELLOW CHOICE[/TD]
[TD]BUSHEL[/TD]
[TD="align: right"]27.49[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]999363[/TD]
[TD] GARLIC CLOVE JUMBO 5#[/TD]
[TD]5#[/TD]
[TD="align: right"]13.37[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]654531[/TD]
[TD] TOMATO DIST GRAPE YELLOW -- **SP**[/TD]
[TD]12 PT[/TD]
[TD="align: right"]39.90[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]705540[/TD]
[TD] POTATO YUKON GOLD "A" *COLORADO*[/TD]
[TD]50#[/TD]
[TD="align: right"]17.65[/TD]
[/TR]
</tbody>[/TABLE]

 

[onlyadrafter]

Hello,

not sure what you are trying to achieve, but to get the price from Sheet 2, Col D when Sheet 1 Column F matches Sheet 2 Column A you need this formula

=INDEX(Sheet2!D:D,MATCH(Sheet1!F3,Sheet2!A:A,0),0)