Web query and formula issue...

[fdgsogc]

I'm using a web query to grab data from an HTML table. In my last row of the HTML table, I have included R1C1 style formulas:

=R[-1]C-R[-2]C

The reason I do this is because I'm not sure how many rows I'm going to have in my table, but I know that I want the two previous rows subtracted from one another.

I tried using offset function but it requires a known A1 reference point.

PROBLEM: The web query works fine. But the R1C1 formula does not work. It displays as a formula, until I enter the cell into edit mode and simply hit the enter key.

Any ideas what this behaviour is about?

 

[PHV]

Don't put the formula in the HTML table but in the row just below the QueryTable range.
Don't forget to set the FillAdjacentFormulas property to True.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

PHV - doesn't FilAdjacentFormulas work on formulae to the SIDE of the Querytable rather than below ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

Geoff, I do it all the time with automatically refreshed QueryTables: lines AND columns totals are recalculated.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[xlbo]

cool - always thought it was just the columns next to the querytable that got recalced - thanks

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question