Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change column letters to correct ones in many formulas automatically? | Links and Linking in Excel | |||
How to change column letters to correct ones in many formulas automatically? | Excel Worksheet Functions | |||
how can i get formulas in excel to copy and paste? | Excel Worksheet Functions | |||
formulas to numbers | New Users to Excel | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |