Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Location
    London, Ontario
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing XML into a template (2003 Pro)

    Hello All

    I have an Excel template into which I want to import XML. I know how many columns the XML will have, but not how many rows. I have an XML map attached to my template. When I import the data, the resulting XML list pushes existing cells down (i.e. if the list has 7 rows, it pushes all the cells that already contain data down by 7 rows). In my template a have a column on the right that contains SUM() formulas to total the cells in the row. But when I import the XML data, the formulas get updated. For example, in K7 I have a formula SUM(C7:J7), but when I import the XML data into row 7 the formula gets bumped to SUM(C8:J8), with a return value of 0. Is there any way to stop this behaviour?

    Thanks
    Roger Shuttleworth
    London, Canada

  2. #2
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing XML into a template (2003 Pro)

    Change your formula
    SUM(C7:J7)
    to absolute references
    sum($c$7:$j$7) ??????????

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing XML into a template (2003 Pro)

    Change your formula to:

    <pre>=SUM(INDIRECT("C7:J7"))
    </pre>

    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing XML into a template (2003 Pro)

    Inserting cells above the cells covered by the formula and shifting cells down will still cause the reference to update, even if you make it absolute. Making the cell references absolute only affects copying the formula to other cells, not moving the referenced cells.
    Legare Coleman

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing XML into a template (2003 Pro)

    Probably, the XML data is treated as a so called List. If the SUM functions are alongside the xml data, try if you can expand the list to include the formulas. Select the xml range and choose Data, List, Resize list. Now drag accross the range including the formulas and hit OK.
    HTH.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •