Formel durch ihr Ergebnis ersetzen

[Brummer]

Hallo zusammen,

ich versuche einen Weg zu finden, eine Formel durch ihr Ergebnis zu ersetzen. Ich möchte eine Aktion durchführen die bewirkt, dass das Zelllergebnis zur Formel in dieser Zelle wird. Der hintergrund ist folgender:

In der Frima beziehe ich mich immer auf identisch aufgebaute Dateien, die zum Jahreswechsel in einen anderen Namen bekommen. Alle Dateien hatten im letzten Jahr die Jahreszahl 2019 im Namen und für dieses Jahr habe ich alle auf 2020 umgestellt. In dem Moment, in dem ich die Namen der Dateien auf 2020 ändere, muss ich auch die externen Bezüge anpassen die ich in meinen Tabellen nutze. Bisher bin ich durch meine Tabellen gegangen und habe alle 2019-Werte durch 2020-Werte ersetzt. Ich möchte allerdings gerne eine Zelle haben, in der ich eine Jahreszahl vorgebe und sich daraufhin alle externen Bezüge anpassen, sodass ich mir das Suchen und Ersetzen künftig sparen kann.

Der externe Bezug soll am Ende so aussehen.

=[Datei_2020.xlsx]Tabelle1'!$B$2

Die Jahreszahl soll aus der Zelle A1 gezogen werden. Somit wären alle Bezüge wie folgt aufgebaut

="([Datei_"&A1&".xlsx]Tabelle1'!$B$2)"

Als Ergebnis erhalte ich nun den externen Zellbezug, den ich mir wünsche ([Datei_2020.xlsx]Tabelle1'!$B$2). Nur leider gibt Excel mir nur den Pfad wieder, wendet ihn aber nicht an. Eine Indirekt-Formel kommt an dieser Stelle nicht in Frage, weil ich auch auf geschlossese Dateien zugreifen muss.

Meine Überlegung ist an dieser Stelle, dass mein Ergebnis (Also der Dateipfad: [Datei_2020.xlsx]Tabelle1'!$B$2) die hinterlegte Formel ="[Datei_"&A1&".xlsx]Tabelle1'!$B$2" ersetzt und damit ein funktionierender Bezug zu einer externen Datei hergestellt sein müsste.

Ich hoffe auf eine Lösung, die möglichst kein VBA beinhaltet, da ich sonst einen riesen Berg an Dateien auf .xlsm umstellen müsste.

Vielen Dank im Voraus.

[maninweb]

Hallo,

je nach Aufbau der Quelldaten und ob Du eine moderne Version von Excel einsetzt, könnte man prüfen, ob PowerQuery verwendet werden
könnte: Menüband / Daten abrufen und transformieren / Daten abrufen / ... und Aus Datei oder ggf. Aus Ordner.

Gruß

[Kuwer]

Hallo Brummer,

Ich hoffe auf eine Lösung, die möglichst kein VBA beinhaltet, da ich sonst einen riesen Berg an Dateien auf .xlsm umstellen müsste.

nicht unbedingt, da Du das Makro in der PERSONAL.XLSB unterbringen könntest. Ganz rudimentär für die aktive Zelle dann dieser Code:

Code:

Public Sub CreateFormula()
  With ActiveCell
    .Formula = "=" & .Value
  End With
End Sub

Allerdings ist dann der Bezug zu A1 auch wieder weg (für die Umstellung auf 2021).

Gruß Uwe