Saturday, December 1, 2012

yogi_Compute From Sheet Named Data Difference Between Current Week And Prior Week Values -- Current Week Will Always Be Column B With Previous Weeks Pushed To Right

                                          Google Spreadsheet   Post  #903
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI    Dec 1, 2012
user TerraAnt said:(!category-topic/docs/spreadsheets/q3HlZgtRWps)
How to keep formula from changing? 
I have a spreadsheet with two sheets - one for keeping my data, the other for calculating and displaying this data.

The data sheet has each column containing information from a single week. Each week a new column is added (column B is inserted, with old data getting pushed to the right).

The formula sheet is supposed to go into the data sheet and pull up information from column B (current week's data) and from column C (previous week's data), do bunch of stuff to that data and display the results.

It all works fine, until I insert a new column B in my data sheet. That's when all my formulas change and start reading from columns C and D (that's where old data has been pushed to).
Here's my formula:

=IF(ISBLANK(Data!C2),"- - -",Data!B2-Data!C2)

I need this thing to always look at columns B and C, instead of following my old data to it's new location.

following is a solution to the problem