How to keep formulas constant
 

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.

How to keep formulas constant
 

Hi!

Try this:

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

Biff

wrote in message
oups.com...
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.

How to keep formulas constant
 

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 :D

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


Biff wrote:
Hi!

Try this:

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

Biff

wrote in message
oups.com...
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.

How to keep formulas constant
 

You're welcome. Thanks for the feedback!

Another way:

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

Biff

wrote in message
oups.com...
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 :D

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


Biff wrote:
Hi!

Try this:

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

Biff

wrote in message
oups.com...
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.

How to keep formulas constant
 

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!


Biff wrote:
You're welcome. Thanks for the feedback!

Another way:

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

Biff

wrote in message
oups.com...
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 :D

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


Biff wrote:
Hi!

Try this:

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

Biff

wrote in message
oups.com...
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.