# Formula doesn't update itself



## Bandito1 (Dec 19, 2022)

Hello all,

I use the following formula; 
	
	
	
	
	
	



```
=IF(AS22="";"";SUMPRODUCT(--('\\nltilnetapsnas1\PUBLIC\Supply Chain\Planning\Sales Order Report\[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$J:$J=AS22)))
```
I specific use SUMPRODUCT cause it should update even if the source file is closed.

It doesn't; i just opened the file and the data it showed was 0.
After i hit enter when i click the formula it updates.

The calculation options is set on automatic

Someone knows why the data doesn't update?


----------



## xlhelp15 (Dec 22, 2022)

Bandito1 said:


> Hello all,
> 
> I use the following formula;
> 
> ...


Hi - Have you tried the enable iterative calculation part ?


----------



## Alex Blakenburg (Dec 23, 2022)

I know you said you had calculations turned on but do you have all the Link setting for automatic update turned on as well ?
And do you have any event macros running that might be turning it off.

Data > Edit Links
• Update Automatic
• Startup Prompt - at least the 1st or 3rd option (not the 2nd option)


----------



## Bandito1 (Dec 24, 2022)

Ah!

It was on; Don't display the alert and don't update automatic links (2nd option)
Now i remember why it was on that; my excel turns very slow when links will be updated.

Is there a way through for example VBA that i can manage which sheets / cells are updated and which are not?


----------



## Alex Blakenburg (Dec 24, 2022)

I am pretty sure it is a workbook setting and you can't you can't turn it on at the range or worksheet level.
How many workbooks and worksheets are you linking to ?
Is using Power Query to pull in the data an option ? You can then control the refresh and it will be much faster.

I am not a big fan of having linked workbooks. They slow your workbook down too much and I prefer to have a point in time snapshot that I import so that I have control over when things change and manage versions.


----------



## Bandito1 (Dec 24, 2022)

It is linked to two workbooks.

Power Query is new for me. 
Is it hard to learn?


----------



## Alex Blakenburg (Dec 24, 2022)

The basics are pretty straight forward.
It depends on how your data you are linking to is laid out as to whether you can use it for your purpose.


----------



## Bandito1 (Dec 24, 2022)

Well for example;

I got this now; see 1


I managed to get the total with power query; 



Is it possible to get it in the same lay-out as i had?
Now it's green with a header above it. 
I can transform this all so it looks like in my example picture?


----------



## Alex Blakenburg (Dec 24, 2022)

Not sure which layout you are after.
The grid with Jan-Dec is an ideal layout for power query.
You can pull it into your main workbook and use that in your formulas instead of having external formula links.
You can then refresh it manually or from a button or use a worksheet event to refresh the data.


----------



## Bandito1 (Dec 24, 2022)

So power query will make my workbook faster?

And one question i need to know before diving fully into it;

The get data file i link to with power query is expanding each day.
It seems like i load the workbook into power query. Is it "set in stone" then and i need to reload it each day or is it being updated when data is added?


----------



## Bandito1 (Dec 19, 2022)

Hello all,

I use the following formula; 
	
	
	
	
	
	



```
=IF(AS22="";"";SUMPRODUCT(--('\\nltilnetapsnas1\PUBLIC\Supply Chain\Planning\Sales Order Report\[Tracker QA on the floor New.xlsm]Tracker QA on the Floor'!$J:$J=AS22)))
```
I specific use SUMPRODUCT cause it should update even if the source file is closed.

It doesn't; i just opened the file and the data it showed was 0.
After i hit enter when i click the formula it updates.

The calculation options is set on automatic

Someone knows why the data doesn't update?


----------



## Alex Blakenburg (Dec 24, 2022)

You might want to test it but if you right click on the query, you should be able to set it to refresh when you open your workbook. I have logged off for the day.
It doesn’t update when new data is loaded until you initiate a refresh.


----------



## Bandito1 (Dec 24, 2022)

So if i get it right;

I have to do; data -> get data -> from file -> from workbook each day?
Since the file is updated each day with data. The data is expanding each day


----------



## Alex Blakenburg (Dec 24, 2022)

No right click in query select refresh. Similar to a pivot table refresh.


----------

