Spreadsheet formula

[ssbns]

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
Item Code	Item Name	Qty	Item Code	Item Name	Qty
A1	Item 1	5	A17	Item 17
A2	Item 2		A18	Item 18
A3	Item 3		A19	Item 19
A4	Item 4		A20	Item 20
A5	Item 5		A21	Item 21
A6	Item 6		A22	Item 22
A7	Item 7		A23	Item 23
A8	Item 8		A24	Item 24
A9	Item 9		A25	Item 25
A10	Item 10		A26	Item 26
A11	Item 11		A27	Item 27
A12	Item 12		A28	Item 28
A13	Item 13		A29	Item 29
A14	Item 14		A30	Item 30
A15	Item 15		A31	Item 31
A16	Item 16		A32	Item 32
Items Received/Sold/Returned Details
Item Code	Item Name	Date	QTY item Received	QTY Defective Item Returned	QTY Item Sold
A1	Item 1	Oct-01-2017	2
A2	Item 2	Oct-01-2017	4
A18	Item 18	Nov-01-2017	4
A19	Item 19	Nov-01-2017	6
A1	Item 1	Oct-15-2017		1
A2	Item 2	Oct-15-2017			2
A19	Item 19	Nov-12-2017		1
A18	Item 18	Nov-14-2017			1
A1	Item 1	Oct-01-2017	2

<tbody>
</tbody>

 

[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

Thanks for your reply. Please see the columns and lines below.
Columns->	B	C	D	E	F	G
Lines	MASTER INVENTORY FORM
4	Item Code	Item Name	Qty	Item Code	Item Name	Qty
5	A1	Item 1	5	A17	Item 17
6	A2	Item 2		A18	Item 18
7	A3	Item 3		A19	Item 19
8	A4	Item 4		A20	Item 20
9	A5	Item 5		A21	Item 21
10	A6	Item 6		A22	Item 22
11	A7	Item 7		A23	Item 23
12	A8	Item 8		A24	Item 24
13	A9	Item 9		A25	Item 25
14	A10	Item 10		A26	Item 26
15	A11	Item 11		A27	Item 27
16	A12	Item 12		A28	Item 28
17	A13	Item 13		A29	Item 29
18	A14	Item 14		A30	Item 30
19	A15	Item 15		A31	Item 31
20	A16	Item 16		A32	Item 32
21	Items Received/Sold/Returned Details
22
23	Item Code	Item Name	Date	QTY item Received	QTY Defective Item Returned	QTY Item Sold
24	A1	Item 1	Oct-01-2017	2
25	A2	Item 2	Oct-01-2017	4
26	A18	Item 18	Nov-01-2017	4
27	A19	Item 19	Nov-01-2017	6
28	A1	Item 1	Oct-15-2017		1
29	A2	Item 2	Oct-15-2017			2
30	A19	Item 19	Nov-12-2017		1
31	A18	Item 18	Nov-14-2017			1
32	A1	Item 1	Oct-01-2017	2

<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> </tbody>

 

[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.