I have a spreadsheet where sheet 1 (e.g. A1:E10) is monthly data in the form of a table created from imported .XML data (and which allows Excel to determine the structure of the table); and sheet 2 which performs analysis on this data via formulae which reference explicitly the area of the data in sheet 1 (e.g. A1:E10).
(To import the .XML data I am selecting cell A1 in Sheet 1 and then using Excel 2010's Data ---> From other sources ---> From XML data import functionality to import the XML data as a table in Sheet 1. This creates an table object as you would expect with the table header row in row 1 and the data in rows 2 to 10. )
My problem is that when I import new monthly .XML data in the same manner all the row references to sheet 1 in the formulae (e.g. f(Sheet!A1)) in sheet 2 are shifted down so that they start after the newly imported sheet 1 data. E.g. they automatically update to be f(Sheet1!A11).
Is there any way I can tell Excel to maintain cell references in the sheet 2 formulae when new .XML data is imported in sheet 1?
Alternatively is there any way I can import the .XML data as a table in Excel using Excel's functionality to determine the structure of a table but at the same time to output the data in Excel as normal cell data and not as an .XML table object?
Any help would be much appreciated.
