Insert Column Alters Formula- Any way to turn off???

[mcongdon]

This might be an answer that is super easy, but I can't quite figure out how to do it. I have a macro that inserts a column as part of a larger procedure. The problem is that there are formulas based on this data on other sheets that change when the column inserts. For example, the formula might be as simple as

=SUM('Insert Raw Data Here'!Z:Z)+Worksheet!F9

The problem is that when the macro that filters and sorts the data inserts a column, the formula shifts to

=SUM('Insert Raw Data Here'!AA:AA)+Worksheet!F9

Is there any way to turn this feature off in a specific workbook? I can't live without it in everything else, but I need to be able to insert this column without the other formulas changing.

Thanks!

 

[bubba100]

Take a look in the Excel Help for absolute referencing with foumulas. I beleive that will take care of your situation.

 

[mcongdon]

I tried using absolute references, and this works if I were to drag the cell containing the formula to a different location. However, when the data source has a column added into it, the absolute reference will change. For example in cell D1 i have a formula =SUM($A:$A). If I drag that cell to D2 or even F56, the formula will still say =SUM($A:$A). When I add a column to the front of the sheet, it immediately changes to =SUM($B:$B).

 

[SkipVought]

Hi,

I try to AVOID using INSERT & DELETE for this very reason.

Rather, add data at the LAST row in the table and then SORT if need be.

Use the Data > List > Create List feature, and your formulas will 'magically' appear on your new row of data.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mcongdon]

Thanks Skip. I will revert back to the original. The end user asked if I could change it to be easier to read, but I'm starting to realize there was a reason I did it that way in the first place. I never really thought about the insert and delete functions and what the consequences from using them might be.

 

[SkipVought]

They DESTROY references, and you are left with a bunch of #REF! errors.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]