Excel 2007 : Maintain Formula while Sorting Cells

[th3fac3insid3]

I've tried looking for this, but I can't seem to find the solution, maybe I'm not searching for the right terms, I don't know.

So, this is what I'm trying to do:
I have an Excel Workbook, with several sheets.

The first list is a Cost of Supplies sheet, with lists of the supplies I've bought, how much they cost, and where I got them.

The next is an Item Catalog with the item name, description and quantity on hand. I was trying to make a column called "Cost to Make" which used a formula to to add the prices of the supplies from the Cost of Supplies sheet, tell me how I spent to make the Item. The formula looks like this: ='Cost of Supplies'!C6+'Cost of Supplies'!C7+'Cost of Supplies'!F30

On the Cost of Supplies sheet, I use the Sort function to sort the supplies alphabetically. When I went back to the Item Catalog sheet, my formula remained the same and therefore was using the wrong cells and giving me the wrong price.

Is there a way I can sort of have the formula update so that it follows the cells if they are moved during a sort and thus maintains the correct cells and data?

There's probably an obvious answer to this, but I'm fairly new to Excel.

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[th3fac3insid3]

Thanks for the advice. I edited the post and attached a dummy workbook, I used before and after worksheets and I highlighted the cell I'm having issues with.

[NBVC]

What is the actual logic of your formula. Are you adding up the matching colours only? Is there any other logic.. i.e. does quantity, item # or Notes, etc...

Have you looked into SUMIF formula or SUMPRODUCT?

[th3fac3insid3]

So the formula... I used black, green, and red to make the item, so I started the formula by just adding the price of Black, Green and Red by referencing their prices on the Cost of Supplies sheet.

[NBVC]

What if you replace the formula in E2 to:

Please Login or Register  to view this content.