Alternative to "=indirect" to correct ext data table column change behaviour

[andydtaylor]

Hi,

For reasons better known to Microsoft, references to a specific table column name change when my table order changes because of a change in the underlying external data fields. This doesn't happen in SQL, and I don't like it!

I am loathed to embed a sequence of =indirect statements into the sumif statements which pick up table data and places it in my worksheets because it's just a hack and it reduces audit-ability. Is there a better way?

I tried changing my table references in the manner below but unfortunately this does not correct this behavour.

input_modules_staging[fIM.Account Name]
input_modules_staging[[fIM.Account Name]:[fIM.Account Name]]

Thanks,

Andy

 

[andydtaylor]

Oh dear... I must be losing it this early in the morning. I remembered the solution to this after hitting post.

Click on table getting feed from external data.

Design>Properties

Put tick in "Preserve column sort/filter/layout"