How to keep formulas constant

[peteinchina]

I have a spreadsheet which runs a macro that performs a web query. The
macro copies the relevant data from the website and inserts it into a
new column on the spreadsheet - column E in this case. Thus, the old
data is shifted to column F. Now, I want column D to contain a formula
which simply calculates the difference between the new data and the
old, say =E6-F6 for the data in row 6. However even using absolute
references, when the new column is inserted, my column D formulas
change from =$E6-$F6 to =$F6-$G6.

How do I keep the formulas from changing when the new column is
inserted?

Many many thanks for any help on this one.

 

[Biff]

Hi!

Try this:

=INDIRECT("E6")-INDIRECT("F6")

Biff

 

[peteinchina]

Genius!! Of course I need an indirect function! I'd forgotten about
them!

=INDIRECT("E"&ROW())-INDIRECT("F"&ROW())

Now I can copy it down all my rows

Thanks Biff!!!! You've just cured my headache!!!!!

 

[Biff]

You're welcome. Thanks for the feedback!

Another way:

=OFFSET(D6,,1,)-OFFSET(D6,,2,)

Biff

 

[peteinchina]

Thanks for all the advice Biff.

It's working perfectly now - just the way I had envisioned from the
outset. Which is refreshing!!

You're a good man, and this is a damn good group!

Cheers!