Office: Formelbezug automatisch erweitern

Hallo Liebe Forenmitglieder,

ich hoffe sehr, es geht Euch allen gut.
Kennt Ihr eine Möglichkeit, den Bereich z. B. in folgender Formel automatisch z. B. auf $AM$148 oder $AN$148 zu
erweitern, wenn sich die Tabelle vergrössert?
=WENNFEHLER(SVERWEIS(KGRÖSSTE($AM$7:$AM$147;AO7);$AM$7:$AN$147;2;FALSCH);"")

Immer wieder stosse ich darauf, dass Werte nicht (mehr) berechnet werden, weil die Formel zu "kurz" ist. Kennt ihr das auch? Und das würde ich gerne automatisch abschaffen.
Leider suchte ich bereits gut eine Stunde in Youtube und Google ohne richtigen Erfolg.
Meinetwegen schreib ich die "148" auch händisch in eine Zelle, aber dann passen wenigstens alle Bezüge.

Dank Euch für Eure Hilfe und Zeit,
Thomas

PS: Im Übrigen - ich bin echt dankbar, für all Euer Wissen - ihr seid echte Koryphäen!

 

Moin Thomas

Wenn deine Tabelle eine Excel Tabelle ist dann wird die automatisch erweitert und auch die Bezüghe darauf. Außerdem kann man strukturierte Verweise verwenden.

 

Hi,

das einfachste ist es eine intelligente Tabelle (auch STRG-T-Tabelle oder strukturierte Tabelle genannt) zu verwenden. Und sich dann die Formeln zusammenklicken. Dann erhält man sowas wie
=WENNFEHLER(SVERWEIS(KGRÖSSTE(MeineTolleTabelle[SpalteX];AO7);MeineTolleTabelle[[SpalteX]:[SpalteY]];2;FALSCH);"")

Ansonsten kann man auch die letzte Zeile berechnen und INDEX() verwenden:
irgendwo (z.B. XY1) =ANZAHL2($AM:$AM)-6 (Voraussetzung: in AM1:AM6 stehen Werte, ansonsten die -6 anpassen und die Tabelle muss in AM lückenlos gefüllt sein) und dann =WENNFEHLER(SVERWEIS(KGRÖSSTE($AM$7:INDEX($AM:$AM;$XY$1);AO7);$AM$7:INDEX($AN:$AN;$XY$1);2;FALSCH);"")

Die STRG-T-Tabelle würde ich vorziehen.

 

Hall DL_, hallo HKindler,

vielen Dank für die Rückmeldungen und Eure Hilfe. Manchmal kann es so einfach sein
Ich hab das mit der intelligenten Tabelle umgesetzt. Aber gut zu wissen, dass das auch mit
der Formel ginge - solche kniffligen Dinge interessieren mich immer besonders. Also ob das
mit einer Formel zu lösen wäre.

Wünsche Euch ein schönes Wochenende, und natürlich alles Gute weiterhin!
Thomas

 

Ich habe seit 40 Jahren immer eine Leerzeile am Ende in den Bezug genommen ... und dann vor dieser Leerzeile die Tabelle erweitert. So begebe ich mich nicht in die Klauen von Strg-L (mit den scheußlichen Bezügen).

 

Hallo Lupo,
versteh ich Dich richtig? Du hast in der Formel von vorn herein den Bezug um eine Zeile erweitert?

 

@HKindler und @DL_ : Zudem fällt mir gerade noch ein, geht das dann auch in den Formeln der bedingten Formatierung?

 

Eine letzte Frage dazu:

Geht das auch in den bedingten Formatierungen? Oder muss ich da nach wie vor die Formeln auf
die letzten Zellen anpassen? Oder mit der Formel von HKindler =WENNFEHLER(SVERWEIS(KGRÖSSTE($AM$7:INDEX($AM:$AM;$XY$1);AO7);$AM$7:INDEX($AN:$AN;$XY$1);2;FALSCH);"") arbeiten?

Lieben Dank an alle,
Thomas