Thread: Table Cells Referencing Specific Cells

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 what I am trying to do...
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.

Try this

Insert a column after column B
On each row in this column that has a quantity enter the name of the Inventory Source it relates to so you end up with

Home of MS $359.00
                             Home of MS  1 Baseball...
                             Home of MS  1 Sony...
                             Home of MS  1 Aiwa...
                             Home of MS  1 Baseball...

Then in the Unit cost enter this formula and copy down

=IF(SUMPRODUCT((C$4:C$17=C4)*(D$4:D$17))=0, "",SUMPRODUCT((A$3:A$17=C4)*(B$3:B$17)) / SUMPRODUCT((C$4:C$17=C4)*(D$4:D$17))*D4)

Column C is the new column that contains the Inventory Source for each Inventory

I've ended up with this

Home of MS	359			
		Home of MS	1	89.75
		Home of MS	1	89.75
		Home of MS	1	89.75
		Home of MS	1	89.75
				
Axis Reading Storage	25			
		Axis Reading Storage	1	25
				
Frenchtown	26			
		Frenchtown	2	5.2
		Frenchtown	1	2.6
		Frenchtown	1	2.6
		Frenchtown	3	7.8
		Frenchtown	3	7.8

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST