I have a table column reference in my worksheet which is connected to a query table which is a result of a MDX query.
我的工作表中有一个表列引用,它连接到一个查询表,该查询表是MDX查询的结果。
This query can return various numbers of columns however, some column names are still the same.
此查询可以返回不同数量的列,但是,某些列名仍然相同。
My problem is, that if I am referencing a column with some name- example: DashboardTable[[#Headers],['[Measures'].'[AFR'] and then refresh my MDX query, which returns different numbers of columns then my formula in excel changes referencing different column than it previously had. (The AFR column still exists in the query results, but may appear at different position)
我的问题是,如果我引用一个具有某个名称的列 - 例如:DashboardTable [[#Headers],['[Measures']。'[AFR']然后刷新我的MDX查询,返回不同数量的列然后我在excel中的公式更改引用不同于以前的列。 (AFR列仍然存在于查询结果中,但可能出现在不同的位置)
Is there a way to have an "absolut" reference to table column with name AFR in this case regardless of it's position in the MDX query? I can be sure that the column with this name will exist, the position though may differ.
有没有办法在这种情况下对名为AFR的表列进行“绝对”引用,而不管它在MDX查询中的位置如何?我可以确定具有此名称的列将存在,但位置可能不同。
Thank you very much for any help
非常感谢您的帮助
1 个解决方案
#1
0
ok, so I decided to solve it with a match function, where I match column name in the header row. previously I had : =offset(tableName.tableColumn,x,0) and I changed it to
好的,所以我决定用匹配函数来解决它,我匹配标题行中的列名。以前我有:= offset(tableName.tableColumn,x,0),我将其更改为
=offset(A1, x, match("columnname", 1:1 (header row), 0)). I find it a little bit dirtier but is the only solution I was able to find.
= offset(A1,x,match(“columnname”,1:1(标题行),0))。我发现它有点脏,但是我能找到的唯一解决方案。
#1
0
ok, so I decided to solve it with a match function, where I match column name in the header row. previously I had : =offset(tableName.tableColumn,x,0) and I changed it to
好的,所以我决定用匹配函数来解决它,我匹配标题行中的列名。以前我有:= offset(tableName.tableColumn,x,0),我将其更改为
=offset(A1, x, match("columnname", 1:1 (header row), 0)). I find it a little bit dirtier but is the only solution I was able to find.
= offset(A1,x,match(“columnname”,1:1(标题行),0))。我发现它有点脏,但是我能找到的唯一解决方案。