Keep cell position in a formula after data is deleted

[Chuck W]

Hi,
I have a workbook with three sheet: Numerator, Denominator and Area Rate.
The Numerator workbook has a pivot table which is based on an access query.
The column names in the pivot table are months (4/1/08, 5/1/08 etc) while the
row names are departments (5th Floor, 6th Floor). Each month, I refresh the
pivot table, remove the oldest month (ie 4/1/08) and add the newest month of
available data (i.e. 5/1/09). So cell position B6 for the numberator sheet
goes from April 08 data for the 5th floor to May 08 data for the 5th floor.

The denominator data also has department names in the rows and months in the
columns. Each month I receive a pdf with the most current month (May 09). I
paste it into a new column to the right and delete the oldest column of data
(always column C). So column C11 was April 08 data for the 5th floor and is
now May 08 data for the fifth floor.

The Area Rate sheet calculates the Rate by department and month. So the
area rate for May 08 for the 5th floor is in B4. The formula is
=((Numerator!B6)/ (Denominator!C11)). I wanth this cell to divide the values
in these two sheets even though I change my pivot table in the numerator and
delete a column in my denominator. I get an error message in the area rate
formulas after I delete my column C in my denominator. I tried absolute
values but could not seem to get it to work. Sorry for the long note but can
someone help me?

Thanks,

 

[Jim Thomlinson]

There is no direct way to keep the cell reference when you are deleting the
cell. To that end you can use the indirect formula. This will work for you so
long as you do not have too many of these formulas. They are volatile so you
take a performance hit on calculation.

=(Numerator!B6)/ Indirect("Denominator!C11")

 

[TK]

Instead of insert & delete, can you try insert, cut & paste.