Need function to input daily inventory from different sheet

**tonny_hartono** · 03-02-2013, 03:00 PM

May be someone can help me. I have 2 form inventory, first is Store Inventory and the second one is Outlet Inventory.
Unit use in Store Inventory are using "dozen" and "box", but unit in Outlet Inventory using "pcs".
1 dozen = 12 pcs
1 box A = 50 pcs
1 box B = 100 pcs

The formula that I need is to automatically insert a quantity at Outlet Inventory when the Item was transferred from Store Inventory.

For Example : date 1 March, I write transfer 1 dozen ; than at the Outlet Inventory it would be input as 12 pcs.

Thank you.

**fredrs05** · 03-02-2013, 03:08 PM

Please Login or Register  to view this content.

**tonny_hartono** · 03-02-2013, 03:16 PM

Originally Posted by fredrs05

Please Login or Register  to view this content.

Thank you for very fast reply.

But the one that I need is formula for column "add" at Outlet Inventory that automatically link with column "TRANSFER" at Store Inventory

I've tried to use =INDEX(B4:Z9;MATCH(A21;A4:A9;0);MATCH(B18;B2:Z2;0);MATCH("TRANSFER";B3:Z3;0)), but it won't worked.

**dredwolf** · 03-02-2013, 03:38 PM

Try this :Sample Inventory.sol1.xlsx
I copied the second table to another sheet to show the sheet referencing as well
Use conditional formatting to display blanks instead of 0 if you want/need to

Hope this helps

**dredwolf** · 03-02-2013, 03:48 PM

Revision!
Noticed I was accessing the date from the wrong page..it worked, but only because the second table was there, this one corrects that

**tonny_hartono** · 03-02-2013, 04:03 PM

yes....it's work.

thank you very much dredwolf.
thank you excel forum..

**dredwolf** · 03-02-2013, 04:09 PM

You are welcome

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"