Moving Formula 
Author Message
 Moving Formula

Hi I am linked to a web hyperlink and along side the data I have formulas .
every so often the web page updates and insertd 10 rows.
There goes my formulas as they do not move with the insertion.
Any help would be appreciated as to how I anchor these formulas.
Regards
Graham


Thu, 08 Jul 2004 04:09:32 GMT  
 Moving Formula

Quote:

> I am linked to a web hyperlink and along side the data I have formulas .

The absence of replies suggests that no-one has understood what you mean
by this.  A Hyperlink in Excel does not bring data into Excel.  

Do you mean a web query?  
If the data gets longer (more rows) do you mean:
-that the formulas are below the data and are over-written
-or that they need to reflect the greater number of rows but they don't
-or that they need to be copied down alongside the new rows?

Which version of Excel are you using (it always helps if you say)?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup



Mon, 12 Jul 2004 08:44:19 GMT  
 Moving Formula
Thank you Bill  I have Excel 2000
Yes I do mean a web query
And it takes from a1 to h22 this can change.
at least this is the query I have in front of me.
Here is the web page where you may get a sample to see what I mean.
http://www.tabracing.com.au/realodds/2002/1/24/index.html

Click on any Venue and there will be approx 8 races.
I usually right click on race 1 without opening it and Copy Shortcut.
Then paste it in the web query
In column I and J I have formulas for each contestant.
When the race is over it pastes into row 6 about 10 rows with the results
and this causes my formulas to move upwards and throw them out of wack.
I thought there was a solution as in Column B there is a cell that has text
in it
It usally says
 ***OPEN*** [Refresh Odds]
      Now when the race commences it turns to
            ***CLOSED*** [Refresh Odds]

      So I asked for help with a timed Vba to time cycle in column B and
when it finds the latter it runs a macro1 which turns off the refresh period
.
      I can send you the code however it is in the newsgroup and do a search
by my name you will find it easy.
      I hope this is clearer, I have been working on this for a week and am
at my wits end.
      Regards
      Graham


Quote:

> > I am linked to a web hyperlink and along side the data I have formulas .

> The absence of replies suggests that no-one has understood what you mean
> by this.  A Hyperlink in Excel does not bring data into Excel.

> Do you mean a web query?
> If the data gets longer (more rows) do you mean:
> -that the formulas are below the data and are over-written
> -or that they need to reflect the greater number of rows but they don't
> -or that they need to be copied down alongside the new rows?

> Which version of Excel are you using (it always helps if you say)?

> Bill Manville
> MVP - Microsoft Excel, Oxford, England
> No email replies please - reply in newsgroup



Mon, 12 Jul 2004 20:46:29 GMT  
 Moving Formula

Quote:

> In column I and J I have formulas for each contestant.

You should make the formulas in I and J regular (ie so that they can be
copied down).  Set the option on the QueryTable to copy formulas down
when new data arrives (bottom option on the Properties dialog accessed
from the second button on the External Data toolbar).

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup



Tue, 13 Jul 2004 15:23:20 GMT  
 Moving Formula
Yes Bil I have checked every and which way these options to no
avail............
Perhaps there is something in the properties of excel that wont allow
this???????????
although I have inserted rows in the middle of the query and yes it does
move the formulas with the data.
Got me stumped.
Regards
Graham


Quote:

> > In column I and J I have formulas for each contestant.

> You should make the formulas in I and J regular (ie so that they can be
> copied down).  Set the option on the QueryTable to copy formulas down
> when new data arrives (bottom option on the Properties dialog accessed
> from the second button on the External Data toolbar).

> Bill Manville
> MVP - Microsoft Excel, Oxford, England
> No email replies please - reply in newsgroup



Tue, 13 Jul 2004 16:30:59 GMT  
 Moving Formula

Quote:

> Got me stumped.

Me too.  Sorry.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup



Wed, 14 Jul 2004 07:50:18 GMT  
 Moving Formula
Hasn't been show what the formulas look like,
before and after.  Perhaps they are now referring
to the wrong row.

HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Macros:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm



Wed, 14 Jul 2004 11:34:31 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Help moving formula to another worksheet

2. I need help on moving formulas - PLEASE

3. Excel macro to move formulae one column to right each week

4. moving a formula to VBA

5. simple question about formulas and moving cells

6. Moving a formula without the accompanying local reference

7. Moving values - keeping formulas

8. Moving between cells linked by formula

9. Formula to move around in worksheet.

10. formulas adjusting when move cells


 
Powered by phpBB® Forum Software © phpBB Group