Dynamischer Bereich für Dropdown, mehrere Spalten

Bild

Betrifft: Dynamischer Bereich für Dropdown, mehrere Spalten
von: Gaby
Geschrieben am: 18.07.2015 13:16:55

Hallo
Ich bin am Ende meines Lateins mit folgender Aufgabe:
Im Tabellenblatt "Angebotsinfos" habe ich in der Spalte J die Firma und ab Spalte K bis Z sind Personennamen aufgeführt, jedoch je nach Firma nur einen Namen (also Spalten L bis Z sind leer) oder eben mehrere (Anzahl unterschiedlich). Ich möchte nun über den Namenmanager zu jeder Firma (=Namen) den Bereich ab Spalte K bis zur letzten gefüllten Spalte definieren.
Im Tabellenblatt "Eingabe" habe ich eine Zelle mit Datenprüfung. Für die Gültigkeitskriterien habe ich Liste und die Formel =IDIREKT(F2) wobei in F2 der Name der Firma steht.
Kann mir jemand helfen, wie ich im Namensmanager den Bezug richtig definiere, dass ich auch noch Namen ergänzen kann und der Bereich automatisch erweitert wird? Ich will nicht K:Z definieren, wenn es nur einen Namen gibt und sonst alles leer wird im Dropdown.
Ich habe folgende Formeln ohne Erfolg getestet, d.h. im Dropdown erscheint nichts:
BEREICH.VERSCHIEBEN(Angebotsinfos!$K$2;0;0;1;(INDIREKT(AB2)
wobei in AB2 die Anzahl gefüllter Zellen enthalten ist. Hat auch nicht funktioniert, als ich für den letzten Formel-Teil die Zahl 5 eingetragen habe (5 = Anzahl gefüllte Spalten, d.h. Daten von K2 bis O2)
=Angebotsinfos!$K$2:(INDIREKT(AC2))
wobei in AC2 die Adresse der letzten gefüllten Zelle der Zeile enthalten ist (Format $O$2)
Was mache ich falsch? Ich habe immer nur Anleitungen gefunden, wie sich der Bereich dynamisch ausweiten lässt, wenn die Daten fürs Dropdown in einer einzigen Spalte sind, d.h. die Spaltenlänge unklar ist. Wenn ich dies auf mein Problem mit dynamischer ZEILENlänge übertrage, scheint es aber nicht zu funktionieren.

Bild

Betrifft: kein BEREICH.VERSCHIEBEN() kein INDIREKT() ...
von: der neopa C
Geschrieben am: 18.07.2015 13:24:43
Hallo Gaby,
... beide Funktionen brauchst Du dzu demvon Dir angestrebten nicht auch nicht viele verschiedene Bereichsnamen.
Stelle einen kleine Beispielmappe (mit anonymisierten Daten) hier ein und ich kann Dir dann sicherlich konkretere Lösungsmöglichkeit aufzeigen.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: Dynamischer Bereich für Dropdown, mehrere Spalten
von: Sepp
Geschrieben am: 18.07.2015 13:40:54
Hallo Gaby,
so?
https://www.herber.de/bbs/user/98914.xlsx
Tabelle1

 DEFGHIJKLMNOPQR
1      FirmaAnsprechpartner       
2      Firma 1Firma 1 Name 1Firma 1 Name 2Firma 1 Name 3     
3 Firma:Firma 1   Firma 2Firma 2 Name 1Firma 2 Name 2Firma 2 Name 3Firma 2 Name 4    
4 Ansprechpartner:Firma 1 Name 3   Firma 3Firma 3 Name 1Firma 3 Name 2Firma 3 Name 3Firma 3 Name 4Firma 3 Name 5Firma 3 Name 6Firma 3 Name 7 
5      Firma 4Firma 4 Name 1       
6      Firma 5Firma 5 Name 1Firma 5 Name 2Firma 5 Name 3Firma 5 Name 4Firma 5 Name 5   
7               
8               

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
F3Liste =_Firma 
F4Liste =_Name 
Namen in Formeln
ZelleNameBezieht sich auf
F3_Firma=Tabelle1!$J$2:INDEX(Tabelle1!$J:$J;ANZAHL2(Tabelle1!$J:$J))
F4_Name=INDEX(Tabelle1!$K:$K;VERGLEICH(Tabelle1!$F$3;Tabelle1!$J:$J;0)):INDEX(Tabelle1!$K:$Z;VERGLEICH(Tabelle1!$F$3;Tabelle1!$J:$J;0);ANZAHL2(INDEX(Tabelle1!$K:$K;VERGLEICH(Tabelle1!$F$3;Tabelle1!$J:$J;0)):INDEX(Tabelle1!$Z:$Z;VERGLEICH(Tabelle1!$F$3;Tabelle1!$J:$J;0))))
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Sepp


Bild

Betrifft: AW: Dynamischer Bereich für Dropdown, mehrere Spalten
von: Gaby
Geschrieben am: 18.07.2015 17:04:21
Vielen Dank für die schnellen Antworten.
Sepp, ich habe versucht deine Anweisungen auf mein Excel umzusetzen, aber das klappt irgendwie nicht :-(
Ich habe eine Beispiel-Datei erstellt, die dem Original in der Struktur her ähnlich ist, d.h. ich habe auch pro Firma jeweils verschiedene Bereiche und dazu je verschiedene Namen.
https://www.herber.de/bbs/user/98916.xlsx
Danke für eure Hife!

Bild

Betrifft: AW: Dynamischer Bereich für Dropdown, mehrere Spalten
von: Sepp
Geschrieben am: 18.07.2015 17:26:07
Hallo Gaby,
wenn man die Formel an deine Bezüge anpasst, dann geht's auch.
https://www.herber.de/bbs/user/98917.xlsx

Gruß Sepp


Bild

Betrifft: AW: Dynamischer Bereich für Dropdown, mehrere Spalten
von: Gaby
Geschrieben am: 18.07.2015 19:32:10
Super, danke Sepp! Einfach genial.

Bild

Betrifft: AW: Dynamischer Bereich für Dropdown, mehrere Spalten
von: Gaby
Geschrieben am: 18.07.2015 20:02:49
Super, danke Sepp! Einfach genial.

Bild

Betrifft: mit INDEX() ist noch etwas mehr möglich ...
von: der neopa C
Geschrieben am: 18.07.2015 20:18:03
Hallo Gaby, hallo Sepp, hallo sonstig evtl. Interessierte,
... in der von Dir Sepp eingestellten Datei, kann man mit INDEX() kombiniert mit der Funktion SUMME() anstelle ANZAHL2() eine nicht unerhebliche Formelstraffung vornehmen.
Ich würde das auch gerne auf Basis der Orginaldatei von Gaby aufzeigen. Doch wenn ich die von Gaby hochgeladene Datei herunter laden will, erhalte ich statt der ausgewiesenen 98916.xlsx nur die 89916.xlsx :-( Letztere Datei beinhaltet ganz andere Daten und stammt somit offensichtlich nicht von Gaby. Oder?
Mir ist jedenfalls unklar woran das liegt, dass ich immer nur eine falsche Datei herunterladen kann. Kann mir das jemand erklären? Und könnte mir jemand die ursprüngliche Datei noch einmal hochladen bzw. einen funktionierenden Link auf die Originaldatei bereitstellen?
Wünsche einen schönen Abend noch, wer immer das jetzt noch lesen sollte.
Ich gehe für heute jedenfalls erst einmal offline.
Gruß Werner
.. , - ...

Bild

Betrifft: Erklärung (soweit möglich):
von: Luc:-?
Geschrieben am: 18.07.2015 23:21:10
Hallo, Werner;
es kommt mitunter leider vor, dass Dateien auf dem Herber-Server verlorengehen. Die Software vermutet bei lfdNrn solcher Dateien dann einen Zahlendreher und bietet dir eine vorhandene Datei mit ähnlicher lfdNr an. Möglicherweise ist das auch auf einen generellen Software-Fehler (bestimmte lfdNrn bei Durch­Numme­rierung und Umbenennung der Dateien → könnte in regel­mäßigen Abständen auftreten!) zurück­zuführen, den HWH entweder bisher noch nicht bemerkt hatte oder aber nicht korrigieren konnte.
Die Datei noch 1× hochladen kann folglich nur jemand, der die OriginalDatei noch herunter­laden konnte, wahrscheinlich Sepp, oder eben Gaby.
Übrigens, hast du meine Nachrichten erhalten? Den leicht erweiterten PgmCode der UDF NoErrRange könnte ich dir (und ggf robert) bei Interesse ja auch zuschicken. Besteht auch andererseits Interesse, poste ich ihn in einem passenden Thread (bzw auf direkte Nachfrage).
Gruß, Luc :-?

Bild

Betrifft: das erklärt mir einiges ...
von: der neopa C
Geschrieben am: 19.07.2015 15:17:30
Hallo Luc,
... vielen Dank für Deine Infos zu meiner Anfrage hier und auch für Deinen weiteren Nachrichten, die ich alle erhalten habe.
Bin froh, dass ich Gestern nicht meiner (oberflächlichen) Internetrecherche vertraut habe. Danach hätte der Fehler an meinem Betriebssystem (angeblich fehlende /nicht aktuelle DLL) gelegen und in dieses greife ich nur ungern ein.
An einer aktualisierten UDF NoErrRange hätte ich durchaus Interesse, wenn diese bei Anwendung nicht mehr zusätzlich "angestoßen" werden muss.
Einen schönen Sonntag Dir dann noch.
Gruß Werner
.. , - ...

Bild

Betrifft: Ganz ohne zusätzlichen Anstoß geht's leider ...
von: Luc:-?
Geschrieben am: 19.07.2015 15:47:05
…nicht, Werner,
weil mir nicht die Möglichkeiten der MS-Pgmmierer zV stehen (konntest du sicher nicht mehr im verblichenen OX/CP-Forum lesen). Aber der ist so gefasst, dass er entweder ohnehin häufig ausgelöst wird oder sehr einfach ausgelöst wdn kann.
Gruß, Luc :-?

Bild

Betrifft: zumindest auf u.a. F9 solltes es reagieren ...
von: der neopa C
Geschrieben am: 19.07.2015 17:34:21
Hallo Luc,
... bzw. auf jeden Fall bei Datei neu einlesen. Das war in der mir bisher bekannten Version noch nicht der Fall.
Gruß Werner
.. , - ...

Bild

Betrifft: Ja, jetzt ist sie mit einer kleinen Ergänzung ...
von: Luc:-?
Geschrieben am: 19.07.2015 18:15:32
…partiell volatil (nur, wenn ausgeblendete Zeilen/Spalten unberücksichtigt bleiben sollen), Werner;
allerdings würde sie dann immer noch nicht reagieren, wenn (ggf durch [F9]) keine anderen Berechnungen ausgelöst wdn (zB, wenn sonst keine volatilen StandardFktt benutzt wurden). In solchen Fällen sollte in mindestens einer ihrer Fmln noch eine volatile StandardFkt (im 2.Argument) hinzugefügt wdn, wodurch ihre Berechnung unabhängig von anderen Berechnungen würde. (Diese) eine volatile StandardFkt würde schon reichen, damit alle Fmln mit dieser UDF, sofern sie ein 2.Argument≠0 hat, neu berechnet wdn. Alle Fmln mit dieser UDF ohne 2.Argument oder mit 2.Arg=0 würden nicht neu berechnet wdn.
(Schicke sie dir per eMail!)
Luc :-?

Bild

Betrifft: damit funktioniert es auch nur teils...
von: der neopa C
Geschrieben am: 20.07.2015 18:14:29
Hallo Luc,
... und zwar jetzt bei [F9].
Der Formel K33 hab ich z.B. mal =SUMME(NoErrRange(H33:J33;1+MIN(JETZT();0))) zugewiesen. Ohne jeglichen Erfolg. Auch nicht, wenn ich eine volatile Funktion in der ausgeblendeten Spalte habe und/oder außerhalb dieser.
Gruß Werner
.. , - ...

Bild

Betrifft: Da es unter Xl12/2007 genauso fktioniert ...
von: Luc:-?
Geschrieben am: 21.07.2015 01:04:48
…wie ich es beschrieben hatte, Werner,
auch, wenn ich nur in K33 (so wie du) eine volatile Fkt dem 2.Argument hinzufüge, würde ich jetzt eigentlich fragen müssen, ob dein Berechnungsmodus auf manuell gestellt ist, wenn das irgendeinen Einfluss hätte. Aber nein, auch in diesem Falle wird alles neu berechnet. Voraussetzung ist nur, dass das 2.Argument=1 in jeder UDF-Notation vorhanden sein muss. Nur 1× muss es außerdem mit einer volatilen StandardFkt ergänzt wdn, in allen anderen Fmln nicht.
Darüberhinaus fktioniert es für ausgeblendete Zeilen sogar sofort, ohne [F9], was der XlFkt TEILERGEBNIS geschuldet sein mag, die dann wohl bewirkt, dass ihr Argument überprüft wird. Insofern scheint sie nicht teilvolatil zu sein, wie von mir vermutet, sondern eher einen ganz anderen Mechanismus zu benutzen, der evtl tatsächlich in die jeweils mit ihrem 1.Argument ausgewählte Fkt eingreift, was durchaus möglich wäre, da diese viell auch eine der ältesten ist. Aber das kann ich natürlich nicht sicher sagen. Allerdings erstaunt mich dann doch, dass sie mit unzusammen­hängenden Bereichen umgehen kann, aber das mag ja ggf auch nur ein Indiz für ihr Alter sein.
Die UDF arbeitet jedenfalls so, dass unzusammen­hängende Bereiche erzeugt wdn, ohne F-Werte und ggf auch ohne ausgeblendete. Das kann der Fml-Assi leider nicht darstellen (auch bei SUMME nicht, möglicher­weise auch, weil die UDF in ausgeblen­deten Zellen selbst einen F-Wert liefert), weil er das Ausblenden von F-Werten nicht berück­sichtigt und auch mit der Nicht­berück­sichtigung ausgeblendeter Zellen nicht umgehen kann (bei TEILERGEBNIS wdn auch Werte ausge­blendeter Zellen angezeigt, auch, wenn sie letzt­lich nicht berück­sichtigt wdn). Er zeigt aber wenigstens richtige End­Ergebnisse an und auch bei einge­tretener Volatilität nicht nur Veränderlich. Das deutet darauf hin, dass die VBA-Volatilität entweder anders organisiert ist als die von XlStandardFktt oder der Fml-Assi das nur bei (möglicher­weise gelisteten) volatilen Standard-Fktt anzeigt.
Falls du auch unter Beachtung aller Hinweise nicht die beschriebene Reaktion der UDF erreichen kannst, könnte evtl irgendwas mit deiner Installation faul sein. Da wäre dann roberts Erfahrung mit der UDF von Interesse…
Gruß, Luc :-?

Bild

Betrifft: Nachbemerkung zu TEILERGEBNIS:
von: Luc:-?
Geschrieben am: 21.07.2015 03:39:24
Vermute aber doch eher, dass TEILERGEBNIS die Daten aus dem Bereich lt seinem 2.(Hpt-)Argument so aufbereitet, dass den Fktt, auf die letztlich zuge­griffen wird, dieselben als Daten­feld über­geben wdn, in dem die Werte ausge­blen­deter Zeilen durch nicht in die jewei­ligen Berech­nungen eingehende Dummies ersetzt wdn. Das ist möglich, weil diese Fktt auch Daten­felder ver­arbeiten können. TEILERGEBNIS selbst benötigt aber Zell­Bezüge, damit abgefragt wdn kann, ob und welche Zeilen aus­ge­blendet sind. Deren Werte wdn dann ersetzt. Ausge­löst wird das dann durch die +100 beim 1.Argument, wobei die Fkt hier eine Info von der XlSteuerung zu bekommen scheint, so dass sie sofort auf der­artige Ände­rungen reagieren kann. Das kann ich natürlich nicht mit einer UDF nach­bilden.
Gibt man als 2.Arg von TEILERGEBNIS einen unzu­sammen­hängenden Bereich an, kann der Fml-Assi das eben­falls nicht dar­stellen. Das scheint generell so zu sein und ist auch bei INDEX-Variante2 nicht anders.
Da die UDF aber gerade auch mit solchen Fktt wie TEILERGEBNIS zusammenarbeiten soll, darf sie kein Datenfeld zurück­geben. Bleibt also nur das Entfernen von Fehler- und ggf ausge­blendeten Werten, was einen unzu­sammen­hängenden Bereich ergibt.
Falls die UDF nur zur Darstellung der verbleibenden Werte benutzt wdn soll, ist die zusätzliche Ver­wendung von INDEX-Variante2 in einer mehr­zelligen Matrix­Fml erforderlich.
Luc :-?

Bild

Betrifft: na, da warte ich mal ab ...
von: der neopa C
Geschrieben am: 21.07.2015 08:01:36
Hallo Luc,
... was die Tests von Robert und oder Weiterer ergibt.
Gruß Werner
.. , - ...

Bild

Betrifft: Formel in K33 funkt bei mir......
von: robert
Geschrieben am: 21.07.2015 09:04:50
Hi,
leider bin ich mal wieder von Lucs UDF überfordert ;-)
Aber die Formel in K33 =SUMME(NoErrRange(H33:J33;1+MIN(JETZT();0))) rechnet ein Ergebnis.
Aber wozu dient eigentlich NoErrRange? =Summe(H33:J33) tuts ja auch !
Was hab ich übersehen oder schlecht gelesen oder einfach nicht verstanden?
Gruß
robert

Bild

Betrifft: nein, die einfache Summenformel "tuts" nicht, ...
von: der neopa C
Geschrieben am: 21.07.2015 09:36:10
Hallo Robert,
... weil ja in J33 ein Fehlerwert steht und somit das Ergebnis der Fehlwert wäre.
Das Ziel der UDF ist darüber hinaus (Fehlerwerte zu ignorieren), ein Summenbildung unter Nichtberücksichtung ausgeblendeter u.a. auch Spalten zu ermöglichen.
Das funktioniert bei mir eben nur unter den von mir benannten Einschränkungen.
Die Formel ermittelt in K33 =SUMME(NoErrRange(H33:J33;1+MIN(JETZT();0))) nach der Eingabe das richtige Ergebnis. Blende ich aber eine Spalte z.B. I aus bleibt bei mir das Ergebnis zunächst bei 11 stehen. Es bedarf erst [F9] um das dann richtige Ergebnis 3 zu ermitteln.
Gruß Werner
.. , - ...

Bild

Betrifft: nun mit #NV getestet...
von: robert
Geschrieben am: 21.07.2015 10:04:17
Hi Werner,
gleicher Effekt wie bei dir !
Danke für die Erklärung :-)
Gruß
robert

Bild

Betrifft: ich hatte damit gerechnet ...
von: der neopa C
Geschrieben am: 21.07.2015 10:37:03
Hallo Robert,
... und Luc wohl auch, obwohl er anderes erhofft hatte.
Gruß Werner
.. , - ...

Bild

Betrifft: Darüber, dass [F9] erforderlich ist, waren wir ...
von: Luc:-?
Geschrieben am: 21.07.2015 11:23:31
…uns doch einig, hatte ich gedacht, Werner (& robert),
dass das so sein muss, hatte ich doch schon auf OX/CP und jetzt wieder erläutert! TEILERGEBNIS bekommt mit, wenn Zeilen ausgeblendet wdn. Es gibt aber kein Standard­Ereignis in VBA dafür! Nur beim Aus-/Einblenden von Zeilen reagiert die entsprd argumentierte UDF sofort, wenn sie mit TEILERGEBNIS kombiniert auftritt, was dazu führt, dass, falls zuerst eine Spalte und gleich danach eine Zeile aus-/eingeblendet wird, alles neu berechnet wird und dann [F9] nicht erforderlich ist. Bei umgekehrtem Vorgehen aber schon, da TEILERGEBNIS von sich aus nicht auf Spalten-Ein-/Ausblenden reagiert (das war von den Pgmmierern so gewollt, wie man der XlHilfe entnehmen kann).
Mir schwebt zwar noch etwas Anderes vor, was ich evtl mal ausprobieren werde, um dieses Problem ohne [F9]-Einsatz in den Griff zu bekommen, aber das muss 1. warten und 2. auch nicht von Erfolg gekrönt sein.
Übrigens, deine Konstruktion mit MIN und JETZT ist unnötig kompliziert. 1^JETZT() reicht als 2.Argument, aber das ändert nichts daran, dass auch deine Variante bei mir mit anschld [F9] bzw Edit-DoppelKlick in beliebige Zelle fktioniert. Sobald eine Berechnung läuft, wdn auch alle UDF-Einsätze mit vorhandenem 2.Argument ≠0 neu berechnet.
Aber, falls ihr [F9] in diesem Fall einsetzt und sich trotzdem nichts tut, werde ich das nochmals in neutraler Umgebung testen.
Gruß, Luc :-?

Bild

Betrifft: mit [F9] geht es ...
von: der neopa C
Geschrieben am: 21.07.2015 12:52:03
Hallo Luc,
... das schrieb ich ja schon. Mir persönlich würde das für die seltenen Anwendungsfälle auch reichen. Allerdings Ziel sollte mE schon sein ohne [F9] auszukommen.
Die Konstruktion mit MIN und JETZT hatte ich bewusst so kompliziert angesetzt, um Eventualitäten abzufangen.
Gruß Werner
.. , - ...

Bild

Betrifft: Na, dann iss ja jut! ;-) Allerdings frage ich ...
von: Luc:-?
Geschrieben am: 21.07.2015 14:50:42
…mich gerade, welche Eventualitäten du abfangen wolltest, Werner,
1^(+IrgendeineZahl) ergibt immer 1, 1^(-IrgendeineZahl) immer noch einen Wert >0 (solange die Rechen­genauig­keits­grenzen von Xl nicht über­schritten wdn) und damit letztlich WAHR, was hier benötigt und aus einer Zahl ≠0 auch erzeugt wird. Erst am 1.1.10000 würde das ggw JETZT wohl einen Fehler liefern, den du so auch nicht abfangen könntest und damit ein FALSCH oder eher die Arbeits­verweigerung der UDF verur­sachen würde.
1+JETZT() ergibt auch immer WAHR, 1-JETZT() nur am 1.1.1900, 00:00:00 Uhr, FALSCH. Bei 1*JETZT() sieht's ähnlich aus.
Etwas anders sähe es bei Verwendung von ZUFALLSZAHL() aus. 1*ZUFALLSZAHL() bzw nur ZUFALLSZAHL() könnte da schon eher mal FALSCH ergeben.
Wie gesagt, ohne [F9] würde wohl ein Einklinken in die XlSteuerung erfordern, wofür ich noch keine Alternative gefunden habe. Wenn mal doch, melde ich mich wieder zu diesem Thema.
Luc :-?

Bild

Betrifft: Mir ist noch eine Automatisierungsmöglichkeit ...
von: Luc:-?
Geschrieben am: 22.07.2015 03:28:36
…eingefallen, Werner & robert,
die hier zu finden ist.
Luc :-?

Bild

Betrifft: hast Du schon die neue Fassung getestet? ...
von: der neopa C
Geschrieben am: 22.07.2015 18:17:59
Hallo Robert,
... zu welchem Ergebnis bist Du gelangt?
Gruß Werner
.. , - ...

Bild

Betrifft: Sorry, kann momentan nicht.....
von: robert
Geschrieben am: 23.07.2015 10:41:22
Hi Werner,
habe für einen Freund eine dringende Änderung in seinen
Hausverwaltungsprogrammen zu erledigen!
Hab aber gelesen, dass es bei Luc funktioniert.
Gruß
robert

Bild

Betrifft: nun, das reißt uns ja nicht aus ...
von: der neopa C
Geschrieben am: 23.07.2015 12:59:27
Hallo Robert,
... kann mich eh erst im August damit noch mal auseinandersetzen. Wäre ich jedoch schon an Deinen Testergebnissen interessiert.
Gruß Werner
.. , - ...

Bild

Betrifft: Hi Luc, hab auch Interesse ;-) Gruß
von: robert
Geschrieben am: 19.07.2015 18:14:56


Bild

Betrifft: Na klar, du bist ja auch schon vorgesehen! ;-) orT
von: Luc:-?
Geschrieben am: 19.07.2015 18:18:28
Gruß, Luc :-?

Bild

Betrifft: OT: Bspp für robert
von: Luc:-?
Geschrieben am: 20.07.2015 20:02:34
Folgende Anwendungsbspp von NoErrRange hatte ich ursprünglich ins OX/CP-Forum gestellt …

1. Nur Nutzung der HauptFktionalität → Weglassen von Fehlerwerten (speziell als Argument von XlStandard-Fktt, die explizit Zellbereichsbezüge verlangen und dabei auch unzusammen­hängende Bereiche verarbeiten können) → UDF agiert nicht-volatil:

 HIJKLM
311611Formeln:  
322712H34[:J34;H38:J38]:=TEILERGEBNIS(9;NoErrRange(H31:H33))
3338#NAME?H39[:J39]:=TEILERGEBNIS(9;NoErrRange(H31:H38))
3462123H40:=SUMME(H39:J39)
35#NV913I40:=TEILERGEBNIS(9;NoErrRange(H31:J39))
3641014J40: {=SUMME(WENNFEHLER(H31:J39;""))/3}
375#NULL!15K40:=SUMME(NoErrRange(H31:J39))/3
3891942L40:=SUMMEWENN(H31:J39;">0")/3
39154065M40:=SUMMEWENN(NoErrRange(H31:J39);">0")/3
40120120120120120#WERT!

2. Zusätzlich Nicht-Berücksichtigung ausgeblendeter Zeilen und Spalten → UDF agiert dann volatil (dynamische Reaktion auf Aus-/Einblenden aber nur, wenn Berechnungen ausgelöst wdn, was man ggf immer durch Aufnahme einer volatilen XlStandardFkt in Arg2 mindestens einer Anwendung der UDF - hier in H40 - erreichen kann → Reaktion auf [F9] und beliebigen Edit-Doppel­Klick):
 HIJK
31161112
32271214
3338#NAME?3
346#WERT!2329
35#NV91322
3641014#WERT!
375#NULL!1520
385#WERT!2833
3911#WERT!5162
4062 62 
41K31[:K39]:=SUMME(NoErrRange(H31:J31;1))
42H40:=SUMME(NoErrRange(H39:J39;1^JETZT()))
43J40:=TEILERGEBNIS(9;NoErrRange(H31:J39;1))

Luc :-?

Bild

Betrifft: Nachbemerkung
von: Luc:-?
Geschrieben am: 20.07.2015 20:17:09
Habe vergessen zu schreiben, dass rote Zeilen und Spalten im Original ausgeblendet sind, robert;
die im 2.Bsp nicht genannten Fmln entsprechen denen des 1.Bsps, sind ggf nur um ein 2.Argument der UDF zu ergänzen. Die blauen F-Werte entstehen nur durch die Ausblendung.
Gruß, Luc :-?

Bild

Betrifft: AW: OT: Bspp für robert
von: AlexG
Geschrieben am: 20.07.2015 20:21:27
Hi Luc
wenn es so weit ist kann ich auch nur mein Interesse kunt tun.
Gruß
Alex

Bild

Betrifft: Das ist möglich, da die UDF nicht allzulang ...
von: Luc:-?
Geschrieben am: 21.07.2015 03:53:27
…ist und schon einmal veröffentlicht wurde, wobei diese leider jetzt verloren ist, Alex;
lies aber auch alle diesbzgl Erläuterungen für robert und neopa in diesem Thread, damit ich das nicht wieder­holen muss. Sie sind deshalb auch etwas ausführ­licher als sonst nötig angelegt.
Du kannst dich dann ja auch am Testen beteiligen und ggf mitteilen, ob du die gleichen Probleme wie neopa hast oder nicht… ;-)
Den UDF-PgmCode werde ich dann in diesem Thread ver­öffent­lichen, damit ich ihn leichter wieder­finde als hier, falls ich ihn mal verlinken will.
Morrn, Luc :-?

Bild

Betrifft: dynamisch abhängige Gültigkeiten (Dropdowns) ...
von: der neopa C
Geschrieben am: 19.07.2015 15:14:41
Hallo Gaby, hallo Sepp,
... mit dem Vorschlag von Sepp, hast Du schon eine sehr gute Formellösung, die im Wesentlichen mit meinen hier geäußerten Vorstellungen einer derartigen Lösung auch übereinstimmen (kein BEREICH.VERSCHIEBEN(), kein INDIREKT() und nur beschränkte Bereichsnamensdefinitionen).
Ich hatte nun aber Gestern auch noch geschrieben, dass ich an Deiner "Original"Datei eine "einfachere" bzw. kürzere INDEX()-Formel aufzeigen würde. Da mir die "Original"Datei bis jetzt leider nicht zur Verfügung steht, beziehe ich mich nachfolgend auf die Datei, die Sepp mit seinem Lösungsvorschlag für den Bereichsnamen: "_Namen" hier hochgeladen hat.
Die Formel von Sepp in seiner Datei lautet dafür:
=INDEX(Angebotsinfos!$L:$L;VERGLEICH(Eingabe!$F2;Angebotsinfos!$K:$K;0)):INDEX(Angebotsinfos!$L:$Z; VERGLEICH(Eingabe!$F2;Angebotsinfos!$K:$K;0);ANZAHL2(INDEX(Angebotsinfos!$L:$L;VERGLEICH(Eingabe!$F2; Angebotsinfos!$K:$K;0)):INDEX(Angebotsinfos!$Z:$Z;VERGLEICH(Eingabe!$F2;Angebotsinfos!$K:$K;0))))
Bis vor kurzen hätte dafür hier als Formel definiert:
=INDEX(Angebotsinfo!$L:$L;VERGLEICH(Eingabe!$F2;Angebotsinfo!$K:$K;0)):INDEX(Angebotsinfo!$L:$Z; VERGLEICH(Eingabe!$F2;Angebotsinfo!$K:$K;0);SUMME((Angebotsinfo!$L$2:$Z$99<>"") *(Angebotsinfo!$K2:$K99=Eingabe!$F2)))
Neu schlage ich nun jedoch vor:
=INDEX(Angebotsinfo!$L$1:INDEX(Angebotsinfo!$L$99:$AZ$99;SUMME((Angebotsinfo!$L$1:$AZ$99<>"")*(Angebotsinfo!$K$1:$K$99=Eingabe!$F2)));VERGLEICH(Eingabe!$F2;Angebotsinfo!$K:$K;0);)
Achtung, Wichtig!: Die Bereichsnamensdefition bzw.- Änderung muss bei einer aktivierter Zelle der Zeile 2 erfolgen!
Den Bereichsnamen "Firma" in Deiner Datei würde ich auch nicht "starr" definieren, sondern so, dass evtl. künftige weitere Firmennamen automatisch berücksichtigt werden. Dies kannst Du mit folgender einfachen Formel gewährleisten:
=Stammdaten!$A$2:INDEX(Stammdaten!$A:A;ANZAHL2(Stammdaten!$A:A))
Weiterhin könnte es in Datei evtl. auch ratsam werden, den Zellen in Spalte D:G des Tabellenblatt "Eingabe" zusätzlich eine bedingte Formatierung zuzuweisen. Damit könntest Du für den Fall dass nachträgliche Änderungen des Firmennamens vorgenommen werden, eine visuelle "Hinweis" (Zellhintergrundfarbe) erscheinen lassen, dass auch der entsprechende Mitarbeiter neu "ausgewählt"/zugeordnet werden muss.
Dazu verweise ich momentan hier nur auf einen früheren Beitrag von mir hier: http://www.online-excel.de/fom/fo_read.php?f=1&bzh=74731&h=74711#a123x in dem ich das für eine ähnliche Problematik auch schon vorgeschlagen hatte. (
Gruß Werner
.. , - ...

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Dynamischer Bereich für Dropdown, mehrere Spalten"