Subtracting along columns.

Good morning,

The scenario is i would like to be subtracting what is in cell say [a1] with what is in cell [b1] and whatever i get the answer to be in cell [c1]. That is not the issue really. Lets assume the range changes such that the following day i add another column that now is cell [c1] so the formula that was then in cell [c1] moves to cell [d1]. The issue now is i would like to have a formula that will in the first day less [a1] with [b1],in the second day it will less [b1] with [c1] etc automatically.

Don't know whether such a problem has been posted before so kindly direct me to that link. Have also attached a sample of what i would like helped out on.

Thank you.

Thanks for posting a sample sheet. Almost certainly there is a better way to do what you are doing. How about you explain the problem you are trying to solve with a spreadsheet, and see if someone can suggest a better solution.

have adjusted the worksheet to give values and an explanation of what is happening.

OK, you are still telling me how you think the problem should be solved, rather than explaining the problem you are trying to solve. I will take a stab and say that you are collecting sales data each day for a range of products. You then want to somehow analyse those sales. See the attached. I have put in a table to capture the data, and a piviot table to report on the data. Now I am not really clear what the net movement is that you are talking about, but I am pretty sure that this approach is better than inserting columns and trying to update formulae as a result.

EDIT: When you add new data to the table, you need to refresh the pivot table. select the pivot table, right click and click refresh

thank you very much for that analysis,however it's not exactly what am looking for. say for today i.e date 17th april [assumption] we have put the details for the products i would like to see the additional items or the items already used up hence subtracting the figures of the previous date ie 16th and the current date i.e 17th.in my analysis tht would be (13.00); (10.00); 7.00; 4.00...now lets go to tomorrow i.e date 18th april i would want to see the same changes but now it will be date 17th details minus the current date i.e 18th i.e 9.00; 15.00; (21.00); (27.00)..now for the day after tomorrow i.e date 19th i would want to see the change between date 18th and 19th [this is the net movement]..is there a formula that i can use to just leave the net movement to be updated automatically day by day with inclusion of new columns to show the items currently there?

Originally Posted by Sam1984

i.e date 17th april [assumption] we have put the details for the products

Please explain what these "details" are. Is it a stock on hand count, is it an order, is it sales data? That would help.