Spreadsheet formula

[ssbns]

[TABLE="width: 667"]
<tbody>[TR]
[TD="class: xl68, width: 889, bgcolor: transparent, colspan: 6"]I need help to setup formula for the following spreadsheet so the Master Inventory form update automatically when I enter Items QTY of Received/Sold/Returned items and also When I ENTER item code theN item name should come automatilly. Your help will be much appreciated.
Thanks

--------------------------------------------------------------------------------------------------------------
MASTER INVENTORY FORM
[/TD]
[TD="class: xl68, width: 889, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Item Code
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item Name
[/TD]
[TD="class: xl63, bgcolor: transparent"]Qty
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item Code
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item Name
[/TD]
[TD="class: xl63, bgcolor: transparent"]Qty
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A1
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 1
[/TD]
[TD="class: xl63, bgcolor: transparent"]5
[/TD]
[TD="class: xl63, bgcolor: transparent"]A17
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 17
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A2
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]A18
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 18
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A3
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 3
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]A19
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 19
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A4
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 4
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]A20
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 20
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A5
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 5
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]A21
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 21
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A6
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 6
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]A22
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 22
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A7
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 7
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]A23
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 23
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A8
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 8
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]A24
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 24
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A9
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 9
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]A25
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 25
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A10
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 10
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]A26
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 26
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A11
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 11
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]A27
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 27
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A12
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 12
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]A28
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 28
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A13
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 13
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]A29
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 29
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A14
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 14
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]A30
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 30
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A15
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 15
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]A31
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 31
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A16
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 16
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]A32
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 32
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, width: 889, bgcolor: transparent, colspan: 6"]Items Received/Sold/Returned Details
[/TD]
[TD="class: xl64, width: 889, bgcolor: transparent"][/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Item Code
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item Name
[/TD]
[TD="class: xl63, bgcolor: transparent"]Date
[/TD]
[TD="class: xl63, bgcolor: transparent"]QTY item Received
[/TD]
[TD="class: xl63, bgcolor: transparent"]QTY Defective Item Returned
[/TD]
[TD="class: xl63, bgcolor: transparent"]QTY Item Sold
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A1
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 1
[/TD]
[TD="class: xl63, bgcolor: transparent"]Oct-01-2017
[/TD]
[TD="class: xl63, bgcolor: transparent"]2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A2
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 2
[/TD]
[TD="class: xl63, bgcolor: transparent"]Oct-01-2017
[/TD]
[TD="class: xl63, bgcolor: transparent"]4
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A18
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 18
[/TD]
[TD="class: xl63, bgcolor: transparent"]Nov-01-2017
[/TD]
[TD="class: xl63, bgcolor: transparent"]4
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A19
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 19
[/TD]
[TD="class: xl63, bgcolor: transparent"]Nov-01-2017
[/TD]
[TD="class: xl63, bgcolor: transparent"]6
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A1
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 1
[/TD]
[TD="class: xl63, bgcolor: transparent"]Oct-15-2017
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A2
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 2
[/TD]
[TD="class: xl63, bgcolor: transparent"]Oct-15-2017
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A19
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 19
[/TD]
[TD="class: xl63, bgcolor: transparent"]Nov-12-2017
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A18
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 18
[/TD]
[TD="class: xl63, bgcolor: transparent"]Nov-14-2017
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"]1
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A1
[/TD]
[TD="class: xl63, bgcolor: transparent"]Item 1
[/TD]
[TD="class: xl63, bgcolor: transparent"]Oct-01-2017
[/TD]
[TD="class: xl63, bgcolor: transparent"]2
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl71, width: 889, bgcolor: transparent, colspan: 6"][/TD]
[TD="class: xl71, width: 889, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[SteveO59L]

Re: Spreadsheet formula Help

have a look at vlookup()

 

[ssbns]

Re: Spreadsheet formula Help

Thanks Steve, can you please write the vlookup() formula then I'll will try it on my spreadsheet. Thanks

 

[MSOTI]

Re: Spreadsheet formula Help

The VLOOKUP will work for providing the item name, but to update the quantity you would use SUMIFS.

It's difficult to write formulas for you because you haven't provided any row or column references.

 

[ssbns]

Re: Spreadsheet formula Help

[TABLE="width: 715"]
<colgroup><col width="64" style="width: 48pt;"> <col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <col width="149" style="width: 112pt; mso-width-source: userset; mso-width-alt: 5449;"> <col width="108" style="width: 81pt; mso-width-source: userset; mso-width-alt: 3949;"> <col width="160" style="width: 120pt; mso-width-source: userset; mso-width-alt: 5851;"> <col width="203" style="width: 152pt; mso-width-source: userset; mso-width-alt: 7424;"> <col width="151" style="width: 113pt; mso-width-source: userset; mso-width-alt: 5522;"> <tbody>[TR]
[TD="class: xl72, width: 953, bgcolor: transparent, colspan: 7"] Thanks for your reply. Please see the columns and lines below.[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Columns->[/TD]
[TD="class: xl71, width: 118, bgcolor: transparent"]B[/TD]
[TD="class: xl71, width: 149, bgcolor: transparent"]C[/TD]
[TD="class: xl71, width: 108, bgcolor: transparent"]D[/TD]
[TD="class: xl71, width: 160, bgcolor: transparent"]E[/TD]
[TD="class: xl71, width: 203, bgcolor: transparent"]F[/TD]
[TD="class: xl71, width: 151, bgcolor: transparent"]G[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]Lines[/TD]
[TD="class: xl68, width: 889, bgcolor: transparent, colspan: 6"]MASTER INVENTORY FORM[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]4[/TD]
[TD="class: xl65, bgcolor: transparent"]Item Code[/TD]
[TD="class: xl65, bgcolor: transparent"]Item Name[/TD]
[TD="class: xl65, bgcolor: transparent"]Qty[/TD]
[TD="class: xl65, bgcolor: transparent"]Item Code[/TD]
[TD="class: xl65, bgcolor: transparent"]Item Name[/TD]
[TD="class: xl65, bgcolor: transparent"]Qty[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]5[/TD]
[TD="class: xl65, bgcolor: transparent"]A1[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 1[/TD]
[TD="class: xl65, bgcolor: transparent"]5[/TD]
[TD="class: xl65, bgcolor: transparent"]A17[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 17[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]6[/TD]
[TD="class: xl65, bgcolor: transparent"]A2[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 2[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]A18[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 18[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]7[/TD]
[TD="class: xl65, bgcolor: transparent"]A3[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 3[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]A19[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 19[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]8[/TD]
[TD="class: xl65, bgcolor: transparent"]A4[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 4[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]A20[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 20[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]9[/TD]
[TD="class: xl65, bgcolor: transparent"]A5[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 5[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]A21[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 21[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]10[/TD]
[TD="class: xl65, bgcolor: transparent"]A6[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 6[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]A22[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 22[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]11[/TD]
[TD="class: xl65, bgcolor: transparent"]A7[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 7[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]A23[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 23[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]12[/TD]
[TD="class: xl65, bgcolor: transparent"]A8[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 8[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]A24[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 24[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]13[/TD]
[TD="class: xl65, bgcolor: transparent"]A9[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 9[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]A25[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 25[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]14[/TD]
[TD="class: xl65, bgcolor: transparent"]A10[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 10[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]A26[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 26[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]15[/TD]
[TD="class: xl65, bgcolor: transparent"]A11[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 11[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]A27[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 27[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]16[/TD]
[TD="class: xl65, bgcolor: transparent"]A12[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 12[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]A28[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 28[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]17[/TD]
[TD="class: xl65, bgcolor: transparent"]A13[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 13[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]A29[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 29[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]18[/TD]
[TD="class: xl65, bgcolor: transparent"]A14[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 14[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]A30[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 30[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]19[/TD]
[TD="class: xl65, bgcolor: transparent"]A15[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 15[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]A31[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 31[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]20[/TD]
[TD="class: xl65, bgcolor: transparent"]A16[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 16[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]A32[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 32[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]21[/TD]
[TD="class: xl70, width: 889, bgcolor: transparent, colspan: 6"]Items Received/Sold/Returned Details[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]22[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]23[/TD]
[TD="class: xl65, bgcolor: transparent"]Item Code[/TD]
[TD="class: xl65, bgcolor: transparent"]Item Name[/TD]
[TD="class: xl65, bgcolor: transparent"]Date[/TD]
[TD="class: xl65, bgcolor: transparent"]QTY item Received[/TD]
[TD="class: xl65, bgcolor: transparent"]QTY Defective Item Returned[/TD]
[TD="class: xl65, bgcolor: transparent"]QTY Item Sold[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]24[/TD]
[TD="class: xl65, bgcolor: transparent"]A1[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 1[/TD]
[TD="class: xl65, bgcolor: transparent"]Oct-01-2017[/TD]
[TD="class: xl65, bgcolor: transparent"]2[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]25[/TD]
[TD="class: xl65, bgcolor: transparent"]A2[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 2[/TD]
[TD="class: xl65, bgcolor: transparent"]Oct-01-2017[/TD]
[TD="class: xl65, bgcolor: transparent"]4[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]26[/TD]
[TD="class: xl65, bgcolor: transparent"]A18[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 18[/TD]
[TD="class: xl65, bgcolor: transparent"]Nov-01-2017[/TD]
[TD="class: xl65, bgcolor: transparent"]4[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]27[/TD]
[TD="class: xl65, bgcolor: transparent"]A19[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 19[/TD]
[TD="class: xl65, bgcolor: transparent"]Nov-01-2017[/TD]
[TD="class: xl65, bgcolor: transparent"]6[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]28[/TD]
[TD="class: xl65, bgcolor: transparent"]A1[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 1[/TD]
[TD="class: xl65, bgcolor: transparent"]Oct-15-2017[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]29[/TD]
[TD="class: xl65, bgcolor: transparent"]A2[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 2[/TD]
[TD="class: xl65, bgcolor: transparent"]Oct-15-2017[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]30[/TD]
[TD="class: xl65, bgcolor: transparent"]A19[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 19[/TD]
[TD="class: xl65, bgcolor: transparent"]Nov-12-2017[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]31[/TD]
[TD="class: xl65, bgcolor: transparent"]A18[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 18[/TD]
[TD="class: xl65, bgcolor: transparent"]Nov-14-2017[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]32[/TD]
[TD="class: xl65, bgcolor: transparent"]A1[/TD]
[TD="class: xl65, bgcolor: transparent"]Item 1[/TD]
[TD="class: xl65, bgcolor: transparent"]Oct-01-2017[/TD]
[TD="class: xl65, bgcolor: transparent"]2[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Macropod]

Re: Spreadsheet formula Help

Cross-posted at: http://www.msofficeforums.com/excel/37323-spreadsheet-formula-setup-help.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[Nutster]

Re: Spreadsheet formula Help

Do not split your item list into multiple groups; just keep going down in a single group of columns; this arrangement will make finding the required information much more difficult and slower.

I would recommend splitting this on two sheets to help organize things better. You would not have to concern yourself as much with running out of room and can use less restrictive ranges in your functions. You could also protect the sheet with your items list to reduce the likelihood of accidental screw-ups.

When you enter an item code, you want the name to appear. In the cell where you want the name to appear, you can use VLOOKUP to find the appropriate item record. For example in C24, I would normally put:

=VLOOKUP(B24, $B$5:$C$20, 2, False)

This VLOOKUP is effectively looking for the first argument (the value in B24, "A1") in first column of the range in the second argument. When it finds the row with that value, it returns the value in column 2 of the range (C, in this case) for that row. Copy that formula down to the rest of the transactions.

Because you have two groups of items, the above formula has to be modified to look in two different places. In C24:

=IFERROR(VLOOKUP(B24, $B$5:$C$20, 2, False), VLOOKUP(B24, $E$5:$F$20, 2, False))

If the first argument to IFERROR does not give an error result, like #N/A, then it returns the first argument. If the first argument does give an error, then the second argument gets returned instead. So this way, if item is not found in the first list, it will look in the second list for it.

When you want to keep track of your inventory, you can use SUMIF. For example, in D5, you can put:

=SUMIF($B$24:$B$32, B5, $E$24:$E$32) - SUMIF($B$24:$B$32, B5, $G$24:$G$32)

The SUMIF looks in the range given in the first argument for the value in the second argument, then adds up the corresponding numbers in the third argument. I am adding the amount received and subtracting the amount sold. The items returned defective are not returned to inventory until they are repaired/replaced. You can then copy-and-paste this formula to each of the Qty cells in your item lists.

 

[ssbns]

Re: Spreadsheet formula Help

It works perfectly, Thank you.