XML Import help

[mnowinski]

Using Excel 2007

I am having problems with reformatting imported XML data.

First step is to retrieve an XML data file from a web location. The data is loaded into a separate worksheet. Unfortunately some of the data fields are not in a usable format. To correct the data for my use, I have a separate worksheet where I copy, and if necessary, manipulate some of the data fields in each input row. Each row of this new sheet reads a corresponding row in the XML table.This new sheet is then used to create pivot tables.

The problem I have is that the XML data is dynamic, often changing every time I refresh the input. What this also does is change the XML table range, most importantly the number of rows brought in. When this happens, it causes the formulas in the second worksheet to get scrambled,. By this I mean that one row will have all of it's formulas pointing to a specific row in the XML table. The next row should be offset to the next row, but somehow it gets out of sequence, often jumping any number of rows.depending on how many input rows the XML feed has each time.

Small example:

........Col A....................Col B........................Col c
=XMLTable!A3.......= XMLTable!B3........=XMLTable!C3
=XMLTable!A4.......= XMLTable!B4........=XMLTable!C4
=XMLTable!A10.... = XMLTable!B10......=XMLTable!C10 Here's where the jump takes place.

I can't come up with a way to get around this issue. Any help appreciated.