Da du noch keine AW hast, erst mal vorläufig:

Die Adressierung von LO hat mich auch lange gefuchst: Du siehst das Prinzip, wenn du =ZELLE("Dateiname") eingibst:

'file:///C:/Verzeichnis/Unterverzeichniss(se)/Datei.Typ'#$Blatt

Diese Struktur ist erheblich komplizierter aufzulösen als bei xl, wo die Datei in [Klammern] steht, es geht aber, ich hab das für Hyperlink (zur Datei ERLEDIGE-F.xls und Blatt EINKAUF mal mit diesem Monster gemacht (kursiv ist der "freundliche Name", also die Info zur Orientierung:

=HYPERLINK(LINKS(ZELLE("Dateiname");FINDEN("|";WECHSELN(ZELLE("Dateiname");"/";"|";LÄNGE(ZELLE("Dateiname"))-LÄNGE(WECHSELN(ZELLE("Dateiname");"/";"")))))&"ERLEDIGE-F.xls'#$Einkauf";"Erledige, Längen-unabhängige Formel")

ich weiß nicht, obs da was kürzeres gibt, wollte im LO-Forum immer mal fragen, habs aber wieder aus den Augen verloren (dort sind übrigens ebenfalls sehr kompetente User, ich rate dir, dich da anzumelden, denn die sind auf OO/LO spezialisiert)

Längen-unabhängige Formel heisst, dass ein Pfad variabler Länge richtig ausgewertet wird, unabhängig auch von der Zahl der Verzeichnis-Ebenen.

Das Prinzip ist, dass der letzte Slash gegen eine Pipe | ausgewechselt und so der Beginn des Dateinamens mit FINDEN("|";... ermittelt wird bzw -anders herum- die Länge der Verzeichnisstruktur. Ich kann heute aber nicht mehr sagen, warum ich nicht gleich nach dem Beginn des Dateinamens gesucht habe.

Ich hatt auch schon die Adresse der Zelle als target eingegeben, auch das ging: Hyperlink steuerte direkt die Zelle an.

Vllt hilft dir das schon weiter, ich muss mich selbst erst wieder eindenken, frag ggf nochmals nach.

...zur Antwort

der (für mich) Einfachheit halber in einem kleineren Bereich und mit fixem Suchwort:

=SVERWEIS("total";INDIREKT(A1&"!"&"B2:E19");3;0)

(also mit weitaus weniger Gänsefüßchen) oder noch kürzer:

=SVERWEIS("total";INDIREKT(A1&"!B2:E19");3;0)

Aber besser durchschaubar ist m.E. diese Formel (sie hat auch in der Praxis noch einige weitere Vorteile):

=INDEX(INDIREKT(A1&"!D:D");VERGLEICH("total";INDIREKT(A1&"!B:B");0))

in Vergleich() hast du hier keinen SpaltenVERSATZ, sondern adressierst in Index() die Auslese-Spalte direkt ebenso wie die Suchbegriff-Spalte, also

=index(wo soll der Wert ausgelesen werden;Vergleich(was ist das Kriterium;wo soll es gesucht werden;WAHR/FALSCH))

Vergleich liefert eine ZellNr im Bereich, bei B:B ist die identisch mit der ZeilenNr.

Beide Formeln sollten aber von Blatt zu Blatt beliebig kopierbar sein, oder willst du die Ergebnisse in einer Liste auf EINEM Blatt zusammenstellen? (also eine Liste aller Total mit den entsprechenden Summen?

Dann sollte eine Liste deiner Blattnamen die Basis bilden und du kannst die Formel nach $-setzen der Zeilen des Bereichs (bei SV) nach unten kopieren. Die Index-Fo ist mit B:B bzw D:D von vornherein so angelegt, dass das nicht nötig ist: B:B etc bleiben beim nach-unten-kopieren B:B etc.

Makro zum Erstellen einer Blattliste:

Sub Blattliste_erstellen()

Dim Blatt

For Each Blatt In Worksheets

If Not IsEmpty(ActiveCell) Then MsgBox "Zielzelle(n) belegt, Abbruch": Exit Sub

ActiveCell = Blatt.Name

ActiveCell.Offset(1, 0).Activate

Next

End Sub

...zur Antwort

Ohne VBA kannst du das Datum / die Uhrzeit sehr schnell manuell setzen, ohne sie schreiben zu müssen (auch weil viele Netzwerke kein VBA zulassen):

Shortcut Datum: Strg+Punkt

Shortcut Zeit: Strg+Doppelpunkt

gehen beide auch nacheinander (sinnvollerweise mit Leerzeichen dazwischen) oder einzeln in Fließtexten.

In LO etc geht dieser Shortcut nicht. (oder anders?)

...zur Antwort

Das geht meines Wissens nur mit einem Ereignis-Makro. Aber wegen der Bezüge müsste ich wissen, wie deine Formel aussieht, in welcher Spalte sie steht und ggf. welcher Eintrag das Setzen der Formel auslösen soll.

Da es sich um ein Datum zu handeln scheint, auch welches Datumsformat.

Wenn abweichend, auch für die bedingte Formatierung, aber da würde ich erst versuchen, ob nicht der Vorschlag von Subopt greift, einen größeren Bereich zu formatieren (die nicht ausdrücklich notwendigen $-Zeichen dazu entfernen!)

...zur Antwort

soll in einem bereich alle "L" zählen würde als Zelleintrag allein schon 1 oder 5 oder 6 L, l, Ll liefern (je nachdem, ob nur groß L, nur klein l oder beide)

ich habe in J14:J17 stehen:

  • von Luft und Liebe leben
  • Grosse L
  • kleine l
  • alle L zählen

=SUMMENPRODUKT((LÄNGE(J14:J17)-LÄNGE(WECHSELN(J14:J17;"L";"")))*1) => 4

=SUMMENPRODUKT((LÄNGE(J14:J17)-LÄNGE(WECHSELN(J14:J17;"l";"")))*1) => 6

=Summenprodukt((Länge(J14:J17)-Länge(Wechseln(Klein(J14:J17);"l";"")))*1) =>10

  • Summenprodukt kann zwar selbst keine Texte verarbeiten, aber die von innen nach aussen(Klammerebenen) generierten Zahlen schon.
  • Wechseln unterscheidet GROSS/klein, aber durch den Trick, nicht die Zelle selbst, sondern Klein(Zelle) zur Verarbeitung einzustellen, geht auch das gleichzeitige Wechseln beider.
  • Da L bzw l gegen Nichts ausgewechselt werden, verringert sich die Länge entsprechend der Zahl der L/l. Die Differenz ist also das genaue Maß für die Zahl der L/l.
...zur Antwort

ist ja lange her, aber vllt ist noch der Weg ohne VBA interessant:

Arbeite mit Bearbeitungszeile. Schreib in eine Zelle (zB H1) =heute(). Klick das vorbereitete Textfeld an. Schreib in die Bearbeitungszeile des Textfelds =H1.

dann wird der Wert aus H1 ins Textfeld übernommen (auch bei anderen Einträgen)

Diese Methode funktioniert NICHT in Libre Office, leider (und ggf anderen).

Es gibt in xl aber auch einen Shortcut fürs heutige Datum: Strg+Punkt.

Und für die Zeit: Strg+:

Beide können auch mitten in einem Fließtext verwendet und am besten mit mit Leerzeichen dazwischen nacheinander gesetzt werden.

funktioniert ebenfalls NICHT in Libre Office

...zur Antwort

Du willst doch sicher für jede einzelne Zelle in beiden Bereichen wissen, ob sie leer sind, du fragst aber VBA, ob der gesamte Bereich leer ist. (was mit isempty gar nicht geht, das wertet nur Einzelzellen aus). VBA ist zwar sehr mächtig, aber auch sehr empfindlich gegen falsche Formulierungen (Syntax). Mach es so, wie ich es in der AW zu deiner vorhergehenden Frage beschrieben habe, dann sollte es gehen!

...zur Antwort

Bin zwar auch ein Fän von For Each...next, aber es geht auch mit einer leichter verständlichen For 1 to ...-Schleife, und zwar so (sofern deine Formel ok ist, hab zwar IIf schon gesehen, ist mir aber nicht geläufig):

  • Sub VarZ1_20()
  • Dim Z as Integer
  • For Z = 1 to 20
  • Worksheets("Tabelle1").Range("C"&Z) = IIf((IsEmpty(Range("A"&Z)) + IsEmpty(Range("B"&Z))) <> 0, "", (Range("A"&Z) - Range("B"&Z) + 1))
  • next
  • End Sub

kannst du hier kopieren (die dicken Aufzählungspunkte werden von VBA ignoriert)

...zur Antwort

ich würde hier nicht den SVerweis nehmen, sondern:

=6-VERGLEICH(A3;{0;27;33;40;46;53};1) oder =6-VERGLEICH(A3;D4:D8;1)

Das geht allerdings nur mit ganzzahligen Noten-Abstufungen. Die {Zahlenfolge} kannst du auch in eine xl-Liste schreiben und dich darauf beziehen (2.Formel).

Ich gehe davon aus, dass das Wort " Punkte" durch Benutzerdefinierte Zellformatierung erzeugt wurde und nicht als Text zusätzlich zur Zahl in den Zellen steht, sonst musst du entweder auch nach "53 Punkte" oder einfacher 53* suchen (Verwendung von Wildcards [? oder *] oder es wird kompliziert.

Willst du mehr wissen, frag als Kommentar in DIESER Antwort, damit ich das sehe.

Und wenn's unbedingt der SVerweis sein muss, ebenfalls. Ich hätte allerdings auch noch weitere Alternativen zum SVerweis, auch für gestückelte Noten (zB Drittelnoten)

Das Beispiel zeigt wieder mal, dass man sich schon beim Anlegen einer Liste Gedanken machen sollte, wie man sie später auswerten will.

...zur Antwort

Da ich die Frage ganz anders verstehe als alle anderen, mein Vorschlag:

=Summenprodukt((A2:A20>=3)*(A2:A20<=8)) liefert die Anzahl aller Zahlen im Bereich A2:A20, die zwischen incl.2 und incl. 8 liegen.

=Summenprodukt((A2:A20>=3)*(A2:A20<=8)*A2:A20) liefert die Summe dieser oben gezählten Zahlen

In eine Zelle "3-8" zu schreiben, ist sehr unpraktisch, da du dann eine Textauflösung bräuchtest (wäre möglich, aber langatmig). Schreibst du dagegen die Grenzen in eine Min- und eine Max-Zelle, kannst du dich direkt darauf beziehen: also Min sei A1, Max sei B1, dann

=Summenprodukt((A2:A20>=A1)*(A2:A20<=B1)) oder analog mit der Summe

Würde mich interessieren, wie die Frage gemeint war bzw wessen Antwort hilft!

...zur Antwort

Inhaltlich möchte ich den anderen AW nichts hinzufügen. Aber ist OO wieder besser geworden oder warum haben so viele OO und nicht Libre Office, das -zumindest lange Jahre- um Größenordnungen besser war, weil ständig aktuell upgedated? (und ebenfalls kostenlos)

...zur Antwort

Prinzipiell kannst du mit B1: =TEIL(A1;FINDEN("AA";A1;1);8) das erste Auftreten einer solchen Nummer rausschreiben. Runterkopieren.

Wie du eine zweite solche Nummer extrahieren kannst, muss ich noch überlegen (aber erst heut nacht wieder, hab aber schon eine vage Idee!)

...zur Antwort

Setz mal ein ' oder " davor, das hilft normalerweise. Oder formatiere die Zelle VORHER auf Text (Zellen formatieren, Zahlen, Benutzerdefiniert, Text , am besten gleich einen größeren Bereich oder die ganze Spalte). (hab allerdings nicht OO, sondern andere Tabcalcs.)

...zur Antwort

=Summenprodukt((C$1:Cn="")*(D$1:Dn="")*(E$1:En=""))

oder Summenprodukt(istleer(C$1:Cn)*istleer(D$1:Dn)*istleer(E$1:En)

Hierbei ist n die letzte Zeile mit den Namenseinträgen. Hast du bei denen allerdings auch Lücken, muss noch ein Kriterium mehr her (ich nehme an, nur der Schreibname reicht?):

=Summenprodukt((A$1:An<>"")*(C$1:Cn="")*(D$1:Dn="")*(E$1:En=""))

...zur Antwort

wie entfernst du denn? Mit der entf.-Taste? Ging bei mir noch immer. Kannst auch den Text einer Einzelzelle in der Bearbeitungszeile markieren und zB mit leerzeichen überschreiben.

Oder hast du in irgendeiner Form verbundene Zellen (zB horizontal über Auswahl zentriert und bist schlicht in der falschen Zelle?

Es ist auch möglich, übers Benutzerdefinierte Zahlenformat einen Text darzustellen, der sollte mit del/Entf aber auch verschwinden.

Oder läuft da ein Aktionscode per µ im Hintergrund? Klick mal auf Code anzeigen im Reiter des Arbeitsblattes!

...zur Antwort

Um etwas Fleisch an die Sache zu bringen: Ich hab mal als Chemiker mithilfe einer Atomgewichtstabelle ein großes Makro geschrieben, das nicht nur Molekulargewichte ausrechnete, sondern auch Formeln so darstellte, dass der Index tiefgestellt und auf lesbar wiedervergrößert worden war. Wenn ein Molekularpartikel gelistet war (zB als Citr, Ace, Succ, Sacch,...) konnte man auch diese auswerten , zB K2HCitr x H2O wurde richtig geschrieben und berechnet, sogar rotes Blutlaugensalz, wenn man es als Formel K3[Fe(CN)6] eingab. Ein davon abgeleitetes Makro stellte auch in Word diese Formeln richtig dar. Berechnet wurde da nichts, aber das Makro erkannte, ob mitten im Text solche Formeln auftauchten, die es dann formatierte. ZB fürs Schreiben von Laborprotokollen oder Doktorarbeiten. (gab auch ein paar Störfaktoren, wenn zB Rechtsbegriffe wie zB Gesetz$7, was als Formel interpretiert wurde. Und nur ganzzahlige Indices waren erlaubt (später unvollkommen verbessert)

Leider hat das Word-Makro nach einem Upgrade von Word dann nicht mehr getan, das xl-µ hab ich zum letzten Mal in xl2003 noch in Aktion gehabt.

In xl hab ich lange Zeit sowas wie einen Versionsmanager (in xl heißt das Szenario, der Name stammt noch aus Lotus-123, wo ich sowas zuerst betrieb) programmiert: Da mir der eingebaute Mgr schon bei 20 verschiedenen Versionsfeldern à ca 1000 Zellen die Datei sprengte, wurden die (aussagekräftig benannten)Versionsfelder in vielen speziellen Dateien gespeichert, von dort automatisch geholt, dort auch geändert oder neu angelegt (es handelte sich um komplexe Versuchsrezepte). Jeweils über Abfrageboxen angewählt (die zugehörige Datei geöffnet) und von dort in eine Rechendatei eingespeist, in der ich mich dann auf die eigentlichen Aufgaben konzentrieren konnte. Hat mir bis zu meiner Pensionierung wertvolle Dienste geleistet.

Es gibt aber auch ganz kleine Makros: zB eines, das eine Einzelformel als Text darstellt (ist in xl ja nur eine Option für alle Formeln eines Blattes). Oder eine als Text geschriebene Formel wieder aktiviert.)

u.v.a.m.

Ich muss dich aber warnen: du steckst eine Unmenge Zeit in sowas rein!

...zur Antwort

Bette das ein in

Dim C

For each C in Selection

...

next C

und ersetze alle Target durch C

...zur Antwort

=G5/B5-1

Zellformat Zahlen, % oder besser Benutzer-definiert so:

0,00%;[Rot]-0,00%;[Magenta]"k.V'änd.";[Blau]"Text !"

Ins Zellformat-Menü kommst du mit Strg+1

Der Abschnitt Text verbirgt hier den Zellinhalt (nur in der Bearbeitungszeile sichtbar)

...zur Antwort

Da es "Alle Hotels" heisst, nehme ich an, da steht eine Liste. Dann funktioniert das mit Sterne, Kinderfreundlich etc nicht: xl sucht da nämlich nach einer benannten Zelle "Sterne", einer anderen "Kinderfreundlich" etc

Wahrscheinlich hast du da Spalten stehen mit der Überschrift "Preis", "Sterne"... aber wo stehen die und was ist da eingetragen? **** oder 4?

Beispiel (Spalte A, dann B usw):

  • A: Hotelname; B:irrelevant; C:Preis; D:Sterne; E:Kinderfreundlich
  • Ambassador; genaue Adresse; 199,50; 5; Ja; --weitere analog--

Formel in der Zeile2=Ambassador (irgendwo rechts, zB in Spalte Empfehlung)

=WENN(UND($C2<=200;ODER($D2>=4;$E2="Ja"));"Empfehlung";"nix gutt")

weitere Oderargumente innerhalb der Oderklammer anhängen, also $F2=...;G2=...

Sind die Sterne dagegen als ***** angegeben, wirds noch komplizierter: Entweder

...;Nicht(istfehler(Suchen("****";$D2;1)));... oder

...;(Länge($D2)-Länge(Wechseln($D2;"*";""))>=4;...

Versuchs mal!

...zur Antwort

kurz:

MsgBox ActiveSheet.Range("E1")

oder

MsgBox Worksheets("Tabelle1").Range("E1")

.Value ist das Vorgabeelement für eine einzellige Range, sollte allerdings der Klarheit wegen trotzdem üblicherweise dazu

Hier eine kurze "Lektion", falls es dich interessiert:

  • Sub MsgBedingtZellinhaltPlusUnbedingtWeiterer()
  • If IsNumeric(ActiveSheet.Range("E1")) Then MsgBox Format(ActiveSheet.Range("E1"), "0.0000") 'Not
  • MsgBox "F1(!): " & Format(ActiveSheet.Range("F1"), "0.0000"): End Sub
  1. 2 VBA-Fkt integriert: IsNumeric() und Format()
  2. Not vor Isnumeric dreht Bedingung um
  3. Textverknüpfung mit & (Festtext + Formelergebnis)
  4. Einzeilige if...then...-Variante, wenn Else/ElseIf/End If nicht erforderlich. Bei Nichtzutreffen der Bedingung gehts einfach weiter im Text (ist weithin unbekannt, da nur unter dieser Voraussetzung sinnvoll). Die zweite MsgBox ist durch die Verknüpfung mit dem Text unterscheidbar
  5. Beachte den DezimalPUNKT in der englischen Zahl, ein Komma würde als Tausendertrennzeichen gelesen(=>andere Dimension!)
...zur Antwort