Hello all,
I'd like to start by saying thank you too all who offer help on this forum, it is always helpful and invaluable to me as i navigate my way through this program!!
I'm not always the best at explaining what I'm trying to do so I have included a link to a screenshot of the sheet i'm working on as a reference...
http://imageshack.us/photo/my-images...pture2dop.png/
OK,
In column "E" where it is labeled "Unit Cost" What I want it to do is look up to price paid and divide it by the number of inventory items FROM THAT SOURCE ONLY, So "Home of MS" currently has 4 items of inventory so $89.75 is correct (Here is the formula i am currently using: =IF([@INVENTORY]="","",SUM($B$7/COUNTA($D$8:$D$11)*[@QUANTITY])), which as you can see works great for the first 4 items.
However, when I get to row 13, "the Last Supper Painting", the unit cost should grab the $25 and divide it by 1 since it's the only item listed there, and likewise when we get down to row 15 it should look at the $26 in cell B14 and use that for its calculations.
So basically Everytime there is a value in column "B" that formula should apply to all the cells under it UNTIL there is a new value in column "B"
After I get this formula to work, the next step is there will be times when I will add an inventory item to the bottom of each inventory source, for instance I might need to insert a row under row 11 and continue adding inventory. since it is above row 12 is should pull the price paid from "B7"
I really hope what I'm asking makes sense. because I'm stumped.
Thank you all so much, I look forward to reading your replies
I'd like to start by saying thank you too all who offer help on this forum, it is always helpful and invaluable to me as i navigate my way through this program!!
I'm not always the best at explaining what I'm trying to do so I have included a link to a screenshot of the sheet i'm working on as a reference...
http://imageshack.us/photo/my-images...pture2dop.png/
OK,
In column "E" where it is labeled "Unit Cost" What I want it to do is look up to price paid and divide it by the number of inventory items FROM THAT SOURCE ONLY, So "Home of MS" currently has 4 items of inventory so $89.75 is correct (Here is the formula i am currently using: =IF([@INVENTORY]="","",SUM($B$7/COUNTA($D$8:$D$11)*[@QUANTITY])), which as you can see works great for the first 4 items.
However, when I get to row 13, "the Last Supper Painting", the unit cost should grab the $25 and divide it by 1 since it's the only item listed there, and likewise when we get down to row 15 it should look at the $26 in cell B14 and use that for its calculations.
So basically Everytime there is a value in column "B" that formula should apply to all the cells under it UNTIL there is a new value in column "B"
After I get this formula to work, the next step is there will be times when I will add an inventory item to the bottom of each inventory source, for instance I might need to insert a row under row 11 and continue adding inventory. since it is above row 12 is should pull the price paid from "B7"
I really hope what I'm asking makes sense. because I'm stumped.
Thank you all so much, I look forward to reading your replies
