SOLVED

Automatic updating of two excel sheets which are connected by formulas and tables

New Contributor

Hello Excel-experts,

 

have a good pre-xmas-time!

 

My issue: I have two excel files: mappe1.xlsx and mappe2.xlsx

Both have tables are linked by xlookups (by names, not by numbers like a1:a10) and shall update each other without opening the second file.

 

I´m opening mappe1.xlsx and the updating of values from mappe2.xlsx should work automaticly in the background.

 

But Excel is not updating at all but telling me it cant find the spreadsheet "mappe2" (which is not referred to in my formulas!) . There are two spreadsheets "tabelle1" and "tabelle2" and the xlookup ist referring to them so I dont understand what to do now.

 

I would be very grateful for any advice!

 

The two files and the mistaken link-warning are in the attachment.

 

 

3 Replies

Hi @DrJedi 

 

it's just not possible to update such formula links in the background, when the linked file is not open.

I generally advise users not to create formula links between different workbooks, as this always leads to trouble sooner or later.

Instead, I propose to create a connection with Power Query. Once that is in place, you can do you updates in the background without opening any other files.

@DTE 

 

Thank you DTE, I see your advice is professional.

The solution demands our company to work with excel on a much higher level as now.

The task for me ist to build up PowerQuery. It will be a lot to bring my colleagues to understand and accept new quality of using excel and me to program it flawless.

 

But thats the reason I like this comment - it´s a kick in the **bleep** to improve!

 

Thx

best response confirmed by DrJedi (New Contributor)
Solution

Hi @DrJedi 

 

I promise you will get a return on the time invest to learn Power Query very soon!

And it's not very difficult to start with it.

If you are interested, you will find some introduction here:

https://www.tabellenexperte.de/einfuehrung-in-power-query-teil-1/

https://www.tabellenexperte.de/einfuehrung-in-power-query-teil-2/