Solved: If Then Statement

[Hoosier03]

I am using excel 2007. I have 3 worksheets. One worksheet has the starting inventory. One worksheet has the current inventory. One worksheet has the sales. How do I write an If/Then statement so if there is a sale entered anywhere in the sales sheet it subtracts out the sale and records it to the current inventory sheet?


Here is what I first wrote.

=IF(store_tracker!D2='current inventory'!B3,'starting inventory'!E3-store_tracker!F2,'starting inventory'!E3)


I then changed it to

=IF(store_tracker!D2:D15000='current inventory'!B3,'starting inventory'!E3-store_tracker!F2,'starting inventory'!E3)

That does not subtract out any of the entries because there are not "blue long sleeve shirts" entries in every cell.


Thanks

[Aussiebear]

Please post your workbook. Currently nothing that you have written makes any real sense as we cannot see the actual values in your workbook.

[Hoosier03]

Current Inventory Sheet
Price Small Medium Large
Blue Long Sleeve Shirt 30 40 5 9
Red Long Sleeve Shirt 30 5 10 8

Starting Inventory
Price Small Medium Large
Blue Long Sleeve Shirt 30 57 7 11
Red Long Sleeve Shirt 30 8 12 10


Sales Sheet

Item Purchased Size Quantity Purchased
Blue Long Sleeve Shirt Small 3
Red Long Sleeve Shirt Small 4
Red Long Sleeve Shirt Small 4



I need to set this up so anytime someone enters data into the sales sheet the current inventory sheet updates.


Thanks,

[Hoosier03]

Sorry, the columns did not paste well. The numbers are price, small, medium, and large units in inventory. After doing some reading I don't think I can do this with an If/Then statement.

[Aussiebear]

To attach a workbook, click on Go Advanced, scroll down to Manage Attachments and follow the proceedure from there. This way we will see your layout and make suggestions as to how you can proceed.

[Bob Phillips]

Try


=INDEX('Starting Inventory'!$A:$E,MATCH('Current Inventory'!$A2,'Starting Inventory'!$A:$A,0),MATCH('Current Inventory'!C$1,'Starting Inventory'!$A$1:$E$1,0))
-SUMPRODUCT(--(Sales!$A:$A='Current Inventory'!$A2)*(Sales!$B:$B='Current Inventory'!C$1))

[Hoosier03]

I ended up using Sumproduct for the whole equation. I could not get index and match to work. I could not attach a file even after clicking Go Advanced. Thanks for your help


='starting inventory'!E4-SUMPRODUCT((store_tracker!$D$2:$D$15000='current inventory'!$B4)*(store_tracker!$E$2:$E$15000='current inventory'!E$2)*store_tracker!$F$2:$F$15000)+SUMPRODUCT((Audit!$B$2:$B$1500 0='current inventory'!$B4)*(Audit!$C$2:$C$15000='current inventory'!E$2)*Audit!$D$2:$D$15000)