Structured Table: Column Reshuffle Issue

[SkipVought]

[tt]
1) The ST is created (in my case it is usually as a result of a query).
I returned 2 columns from an external table: CC_CATEGORY, CC

2) A formula is created in an adjacent column of the table to reference a heading.
I named my table tCC.
My formula is =COUNTIF([CC_CATEGORY], tCC[[#This Row],[CC_CATEGORY]])

3) Edit the External Table Data > Properties -- UNCHECK Preserve column sort/filter/layout

4) Edit the query and simply change the column order by making the last column the first column and return data to Excel

5) Observe the formula ST references to column headings have changed.
My formula is =COUNTIF([CC], tCC[[#This Row],[CC]])
[/tt]
It seems that Excel is using the relative position, rather than the Column Heading as originally entered.

Has anyone else experienced this and what might be the solution?

I have used Create Names in TOP ROW to make named ranges based on table headings, and simply ran the nameing anytime the query ran.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Bump...

Any thoughts?

Otherwise I declare this DOA!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

I was going to take a stab at it the other day, but ran out of time. Could you toss some sample data out there to test with? I wouldn't mind testing with it, but it'll be easier to fully understand the scope if we have some sample data.

Thanks


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

1. on sheet Source

[b]
Name  Dte      Postings[/b]
skip  1/1/2013 10
steve 3/1/2013 20

2. on sheet Query
use MS Query to access data on sheet Source
Initial SQL

SELECT `Source$`.Name, `Source$`.Postings
FROM `C:\Documents and Settings\My Documents\ST_TEST.xlsx`.`Source$` `Source$`

Return data to Excel
Name the query resultset Structured Table tQry

3. formula using table tQry
[tt]
=SUMIF(tQry[Name],"skip",tQry[Postings])
[/tt]
4. MODIFY the External Data Properties -- UNCHECK Preserve column sort/filter/layout

5. Edit the query

SELECT [b]`Source$`.Dte, [/b]`Source$`.Name, `Source$`.Postings
FROM `C:\Documents and Settings\My Documents\ST_TEST.xlsx`.`Source$` `Source$`

and return data to Excel

6. Observe the formula
[tt]
=SUMIF(tQry[Dte],"skip",tQry[Name])
[/tt]
[red]the heading references have changed (shifted)![/red]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}