Structured references and XML refresh

[jimrward]

I have an external XML feed, which varies in the number of columns and rows, the information I need is in different columns positions which I cannot control
my first take was to import the data to an excel table, scan the headers for the columns I need and create a lookup table to use INDIRECT however this bogs it down due to the number of rows and INDIRECT is a volatile nasty function
my second take was to use structured references to pull in and play with the data TABLE1[@name]="Jim" etc this works brilliantly for the one set of data, as soon as I go to my XML table sheet, control A delete the data, and load a different XML file, excel in its infinite or in most cases lack of wisdom decides upon a different table name for the next set of data and of course the formula references collapse with #ref errors.

anyone got any tried and tested methods to keep the XML data as always say TABLE1, or ways to keep the formulae from bombing out

 

[GraH]

Hi,
I'm not sure to entirely get this. But would this work? Have you tried it via Power Query?

 

[jimrward]

okay I have come up with a hokey pokey way of getting around the problem
If I have say 4 xml files, file1, file2, file3, file4, the initial load is with file1 and I get my collated summary information referencing file1, known internally as Table3
if I then rename file1 in windows explorer to file1_done.xml, and then rename file2.xml to file1.xml and within the table worksheet if I refresh the data from the connections section it drags in the new data and does not blow away the references and all is good
I can then repeat the above steps for files 3 and 4
so the new file must be the same name as the initial load file to stop excel auto incrementing the tableN number internally and blowing away the references