Formula doesn't update itself

[Bandito1]

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]

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?

Hi - Have you tried the enable iterative calculation part ?

 

[Alex Blakenburg]

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]

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]

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]

It is linked to two workbooks.

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

 

[Alex Blakenburg]

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]

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]

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]

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?