CODEWITHSUNDEEP
excelpivot-tablepowerquery
erik7970
erik7970

Reputation: 713

Updating a table from a power query changes formula reference

Saw this was previously asked here on SO and had no solution and I can't find a solution through Google either.

I have a workbook with a tab called "Data" containing a table which is updated via a power query and another tab called "Calcs" with formulas referencing the cells in the table from "Data". When I refresh the table, it pulls data via the power query, but when it's done, the formula references change.

For example, before the refresh, I'll have formulas like this in the "Calcs" tab:

=COUNTIFS('Data'!$A$2:$A$26886,$A1060,'Data'!$K$2:$K$26886,'BY CAT'!$B1060)

After the refresh, the references for column A only change to

=COUNTIFS('Data'!$A$10242:$A$26886,$A1060,'Data'!$K$2:$K$26886,'BY CAT'!$B1060)

And it results ina #VALUE! error message.

How can I prevent Excel from creating this reference shift?

Upvotes: 1

Views: 1260

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

You might be able to get around this by using full column references:

=COUNTIFS('Data'!$A:$A,$A1060,'Data'!$K:$K,'BY CAT'!$B1060)

Full column references aren't always a good idea, but it might just work in this case.

Edit:

Table column references would be ideal, TableName[ColumnName]. These should work given that you are reading from a power query generated table.

Upvotes: 0

Related Questions