Formula in Worksheet changes when PowerPivot Refreshed

[cbilliau]

How do I keep a referenced cell in a formula from changing when it is referencing a cell from a PowerPivot calculated field?

I have a formula on a worksheet (Wksht A) that references a cell in another worksheet (Wksht B) which is populated by PowerPivot. The cell being referenced in Wksht B is from a calculated field in PowerPivot.

When PowerPivot refreshes, the formula in Wksht A changes itself to reference the next cell over. I guess since that calculated field is being created excel is trying to find another cell to reference? How do I keep it assigned to that field?

 

[cbilliau]

I was able to solve this problem by using INDIRECT function. INDIRECT changes my cell reference to a string thus preventing Excel from automatically trying to change the cell reference when referenced cell temporarily disappears during a PowerQuery refresh.

INDIRECT Function