Excel not updating formula after change

If you’re new to Power Query, chances are you’re more comfortable doing tricky mathematics using Excel formulas, rather that Power Query formulas.

No shame there, but you’ve probably run into a situation where you set up the formulas, refresh your query and the Excel formulas don’t update in Power Query 's output table.

If you have a range of cells where you have the formulas that you want to copy, you can use a Notepad to quickly copy and paste the formulas.

Unfortunately, VLOOKUP, whilst powerful, simple and easy to use, is one of the least flexible functions in Excel.

For example, on a spreadsheet if I try to copy the formula that was in the "2017 Total" cell of say our our electric service charges into the cell setup for "2018 Total" electric charges the copy function is displaying the dollar value of the total 2017 charges when it should be displaying

For example, on a spreadsheet if I try to copy the formula that was in the "2017 Total" cell of say our our electric service charges into the cell setup for "2018 Total" electric charges the copy function is displaying the dollar value of the total 2017 charges when it should be displaying [[

For example, on a spreadsheet if I try to copy the formula that was in the "2017 Total" cell of say our our electric service charges into the cell setup for "2018 Total" electric charges the copy function is displaying the dollar value of the total 2017 charges when it should be displaying $0.00 until some 2018 charges are input.

The COLUMN() formula will return the column number for the cell which it is referring to.

Here are some examples: This will return 26, because Z is the 26th column COLUMN only calculates based on the cell reference provided.

Note: This adjustment happens when you’re using relative references or mixed references.

In the case of absolute references, the exact formula gets copied.

||

For example, on a spreadsheet if I try to copy the formula that was in the "2017 Total" cell of say our our electric service charges into the cell setup for "2018 Total" electric charges the copy function is displaying the dollar value of the total 2017 charges when it should be displaying $0.00 until some 2018 charges are input.The COLUMN() formula will return the column number for the cell which it is referring to.Here are some examples: This will return 26, because Z is the 26th column COLUMN only calculates based on the cell reference provided.Note: This adjustment happens when you’re using relative references or mixed references.In the case of absolute references, the exact formula gets copied.

]].00 until some 2018 charges are input.

The COLUMN() formula will return the column number for the cell which it is referring to.

Here are some examples: This will return 26, because Z is the 26th column COLUMN only calculates based on the cell reference provided.

Note: This adjustment happens when you’re using relative references or mixed references.

In the case of absolute references, the exact formula gets copied.

.00 until some 2018 charges are input.The COLUMN() formula will return the column number for the cell which it is referring to.Here are some examples: This will return 26, because Z is the 26th column COLUMN only calculates based on the cell reference provided.Note: This adjustment happens when you’re using relative references or mixed references.In the case of absolute references, the exact formula gets copied.

Leave a Reply