Feste Bezüge für gesamtes Tabellenblatt

Forumthread: Feste Bezüge für gesamtes Tabellenblatt

01.03.2006 07:54:53

Marta

Hallo,
ich habe ein Tabellenblatt, in dem sich jede Zelle einen Wert aus einem deckungsgleichen Tabellenblatt einer anderen Datei im Netzwerk holt. Die Datei im Netzwerk beinhaltet eine Lieferantenübersicht, die von mir gepflegt wird. Wenn die Lieferantenübersicht und die Datei, die sich die Werte aus der Lieferantenübersicht holt, gleichzeitig geöffnet sind und ich einen neuen Lieferanten einpflege, stimmt ein Bezug nicht mehr. Deshalb möchte ich die Bezüge fest einrichten.
So ist es jetzt: =WENN('X:\mitarbeiter\ILK-Info\Beschaffung\[Lieferanten.xls]Lieferanten'!A4="";
"";
'X:\mitarbeiter\ILK-Info\Beschaffung\[Lieferanten.xls]Lieferanten'!A4)
So sollte es werden:
=WENN('X:\mitarbeiter\ILK-Info\Beschaffung\[Lieferanten.xls]Lieferanten'!$A$4="";
"";
'X:\mitarbeiter\ILK-Info\Beschaffung\[Lieferanten.xls]Lieferanten'!$A$4)
Ich hoffe ja, dass damit mein Problem behoben wäre, doch ich müßte 12 Spalten x 1000 Zeilen = 12.000 Zellen einzeln verändern. Da sitz ich Jahre.
Geht das nicht irgendwie schneller? Hat da jemand eine Idee?
Danke. Marta

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Feste Bezüge für gesamtes Tabellenblatt

01.03.2006 17:41:25

Ralf

Hallo Marta,
es genügt, die erste Zelle zu verändern und dann die Formel nach unten zu kopieren. In Deinem Fall also 12 Änderungen. Die relativen Zellbezüge kannst Du schnell in absolute ändern, indem Du sie in der Berabeitungszeile markierst und so oft F4 drückst, bis der gewünschte Bezug angezeigt wird. Also A1, $A1, A$1 oder $A$1.
Ciao, Ralf

AW: Feste Bezüge für gesamtes Tabellenblatt

02.03.2006 07:45:45

Marta

Hallo Ralf,
vielen Dank für Deine Nachricht. Das mit F4 wußte ich schon. Es bringt mich jedoch nicht weiter. Wenn ich die Formel mit einem festen Bezug (z. B. =$A$4) versehe, und dann nach unten kopiere, dann bleibt der Bezug erhalten (eben ein fester Bezug!!!). Wenn ich einen normalen Bezug (z. B. =A4) verwende, die Formel dann nach unten und rechts kopiere und dann die Bezüge "fest mache", bin ich wieder an dem Punkt, der mich zum posten im Forum bewegte: Ich muß jeden Bezug in jeder Zelle einzeln bearbeiten. Macht wie gesagt 12 Spalten x 1000 Zeilen =12.000 Zellen = 12.000 Bezüge.
Noch ne andere Idee?
Gruß. Marta

AW: Feste Bezüge für gesamtes Tabellenblatt

02.03.2006 13:00:55

Ralf

Hallo Marta,
offen gestanden verstehe ich Dein Problem nicht so richtig. M.E. nach kannst Du Dir die ganze Wenn-Klausel sparen, denn so wie die da steht kannst Du genausogut schreiben
= 'X:\mitarbeiter\ILK-Info\Beschaffung\[Lieferanten.xls]Lieferanten'!$A$4
Das Ergebnis ist das Gleiche. Wenn Quellzelle = "" dann ist auch Zielzelle = "". Sonst halt nicht.
Da ich die Struktur Deiner Tabellen nicht kenne gehe ich mal davon aus, dass in Spalte A der Zieltabelle alle Werte von Spalte A der Quelltabelle übernommen werden sollen (für alle Zeilen). Das Gleiche gilt für die Spalten B, C, D....
Wenn Du jetzt statt mit relativen Bezügen (A1) mit absoluten Bezügen ($A$1) arbeitest, dann ist klar, dass beim Kopieren sich jede Zelle auf A1 bezieht. Warum benutzt Du keine Mischbezüge? In Spalte A der Zieltabelle also = $A1 und dann runterkopieren, in Spalte B = $B1 und runterkopieren, usw. Das wären nur 12 Änderungen.
Ansonsten, wie gesagt, ich habe keinen Plan davon, was Du sonst meinen könntest.
Ciao, Ralf

AW: Feste Bezüge für gesamtes Tabellenblatt

02.03.2006 15:14:31

Marta

Hallo Ralf,
"...Da ich die Struktur Deiner Tabellen nicht kenne gehe ich mal davon aus, dass in Spalte A der Zieltabelle alle Werte von Spalte A der Quelltabelle übernommen werden sollen (für alle Zeilen). Das Gleiche gilt für die Spalten B, C, D...."
Das hast Du schon mal richtig erkannt!
"...Warum benutzt Du keine Mischbezüge?..."
Hierzu muß ich etwas ausholen. In der "Zielarbeitsmappe" ist nicht nur die "Zieltabelle", sondern es befinden sich noch andere Arbeitsblätter darin. Die "Zieltabelle" benötige ich auch nur, weil die darin enthaltenen Daten als Grundlage für die Auswahlpunkte für ein Formularobjekt (Listenfeld) dienen. Würde ich direkt als Eingabebereich die Zellbereiche in der "Quellarbeitsmappe" und der darin enthaltenen "Quelltabelle" angeben, würde nach dem Öffnen der "Zielarbeitsmappe" und dem Aktualisieren der Verknüpfungen zu den externen Arbeitsmappen ein leeres Listenfeld erscheinen (das habe ich schon probiert!). Die Zieltabelle dient also quasi nur als "Transfertabelle".
Nun ist der Sachverhalt so:
Die "Quelltabelle" in der "Quellarbeitsmappe" (unserer Lieferantenübersicht) wird von mir aktualisiert. Die "Zielarbeitsmappe" mit der "Zieltabelle ist ein s. g. Beschaffungsantrag. Der Mitarbeiter füllt diesen aus und sendet die Datei dann per e-Mail an mich.
Ich importiere alle eingegebenen Daten in eine weitere Arbeitsmappe, die zur Verarbeitung der mir zugesandten Daten dient.
Wenn ein neuer Lieferant (nicht aus dem Listenfeld ausgewählt, sondern Name, Anschrift, Telefon, Fax, ... in speziell dafür vorgesehenen Extra-Zeilen eingetragen) im Beschaffungsantrag eingetragen wurde, so wird dieser mittels eines Makros in die Lieferantenübersicht eingepflegt.
Es werden dabei die Lieferantenübersicht geöffnet, eine neue leere Zeile eingefügt, die Daten (Lieferanten-Nr., Firmenname, Anschrift, Ansprechpartner, Telefon, ...) von links nach rechts in die Zellen der leeren Zeile eingefügt, der Bereich mit dem Namen "Lieferanten" markiert und nach dem Lieferantennamen sortiert.
Jetzt kommt der Knackpunkt:
Wenn während dieses "Einpflegevorgangs" eine Antragsdatei geöffnet ist, werden die Bezüge in dieser aktualisiert, d. h. die Zieldatei (Antrag) verschiebt ihre Verknüpfungen um eine Zeile nach unten. Beim Sortieren der Lieferanten werden die Verknüpfungen nochmals bunt gemischt, so dass eigentlich nichts mehr zusammenpasst.
Deswegen die festen Bezüge. Und wenn ich fest sage, dann meine ich auch fest! ;=))
Ich hoffe ich habe nicht mehr Verwirrung gestiftet als Klärung herbeigeführt. Nur soviel zum Überblick:
- meine Datei, in die ich die Daten aus dem Antrag importiere umfasst folgende Inhalte:
1. Personaldatenverwaltung (auch Datenquelle für Briefe, ... die Personaldaten enthalten), Urlaubsanträge, Datenblätter, Dienstreiseanträge- und Abrechnungen erstellen und drucken, interne Telefonverzeichnisse erstellen
2. Auftragsabwicklung (Anträge importieren, drucken, in Bestellungen umwandeln, ...)
3. Lieferanten pflegen
...
Die Datei ist vor 1 1/2 Jahren entstanden und von mir stetig überarbeitet und weiterentwickelt worden. Viele Wege führen nach Rom, jedoch bedeuten manche davon auch längere Ladezeiten wegen riesiger Dateigrößen. Deshalb versuche ich immer die Funktionalität so hoch wie möglich und die Belastung durch die Dateigröße so gering wie möglich zu halten.
Ich habe auch schon viele nützliche Tipps in diesem Forum erhalten.
So. Genug gelabert! Vielleicht fällt Dir noch was ein. Oder habe ich Dich erst verwirrt und dann totgeschrieben? ;=))
Gruß. Marta

AW: Feste Bezüge für gesamtes Tabellenblatt

02.03.2006 16:01:08

Ralf

Hi Marta,
klar doch. Die Verwirrung ist Dir gelungen...:-) Hauptsächlich aber, weil ich gerade selbst über einem Problem sitze das ich heute noch lösen will und deshalb den Kopf damit schon ausgelastet habe. Ich schau mir also morgen Deine Antwort nochmal in Ruhe an. Versprechen kann ich aber nichts. Alles in allem klingt das Ganze jedoch nach einer Datenbankanwendung. Schon mal darüber nachgedacht das in Access zu realisieren? Ansonsten fiele mir auf die Schnelle nur ein, das Change Ereignis der Tabelle zum Aktualisieren der Listbox zu nutzen. Aber bei der Angabe 'VBA nur mit Recorder' dürfte das zu schwer für Dich sein...
Was mir noch auffiel, durch die Sortiererei passiert ja erst der ganze Schlamassel. Wenn der Nutzer sowieso mit einer Maske arbeitet, dann kannst Du auf das Tabellensortieren verzichten. Hänge die DS unten an und gut ist es. Vergib Deinen Lieferanten eindeutige Indizies und arbeite mit denen. Dadurch ersparst Du Dir 'ne ganze Menge Ärger. Alles in allem riecht das nach einer Menge Arbeit, die wahrscheinlich kaum mal so nebenbei gelöst werden kann.
Ciao,
Ralf

AW: Feste Bezüge für gesamtes Tabellenblatt

02.03.2006 22:10:30

Ralf

Hi Marta,
bevor ich mich lange mit Deinem Problem herumquäle schicke ich Dir lieber ein kleines Makro, dass genau das erledigt, was Du per Hand erledigen wolltest. In 12.000 Zellen die Formel anpassen. Weil ich mir aber nicht sicher bin ob das zu 100% auf Deine Bedürfnisse passt (ich habe den Bereich von A1 bis M zur letzten beschriebenen Zeile benutzt) solltest Du das vorher an einer Kopie ausprobieren.
Das Makro erledigt folgende Aufgabe: Im genannten Bereich wird in jeder Zelle in der Formel
=WENN('X:\mitarbeiter\ILK-Info\Beschaffung\[Lieferanten.xls]Lieferanten'!
E8="";"";'X:\mitarbeiter\ILK-Info\Beschaffung\[Lieferanten.xls]Lieferanten'!E8)
die relative Zelladresse (natürlich mit der jeweils gültigen Zelladresse - hier im Beispiel E8) durch die absolute Zelladresse $E$8 ersetzt. Also in A1 durch $A$1 usw...
Füge das Makro in ein öffentliches Modul ein. Du kannst es dann wie jedes andere Makro auch starten.
'**************************************************************************************
Option Explicit


Sub Formel_korrigieren()
Dim dblLz#, sSp$, zelle, Bereich$, sRow$
Application.DisplayAlerts = False
dblLz = ActiveCell.SpecialCells(xlLastCell).Row
Bereich = "A1:M" & dblLz
For Each zelle In Range(Bereich)
sSp = Mid(zelle.Address, 2, 1)
sRow = CStr(zelle.Row)
zelle.FormulaLocal = "=Wenn('X:\mitarbeiter\ILK-Info\Beschaffung\[Lieferanten.xls]Lieferanten'!$" & sSp & "$" & sRow & _
"="""";"""";'X:\mitarbeiter\ILK-Info\Beschaffung\[Lieferanten.xls]Lieferanten'!$" & sSp & "$" & sRow & ")"
Next
Application.DisplayAlerts = True
End Sub

'***************************************************************************************
Ich hoffe, das hilft Dir einen Schritt weiter.
Ciao, Ralf

Infobox / Tutorial

Feste Bezüge für ein gesamtes Tabellenblatt in Excel

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um feste Bezüge in Excel für ein gesamtes Tabellenblatt einzurichten, kannst du folgende Schritte befolgen:

Öffne dein Arbeitsblatt: Stelle sicher, dass das Tabellenblatt, in dem du feste Bezüge setzen möchtest, geöffnet ist.

Erstelle die erste Formel: Setze in der ersten Zelle deines Zielbereichs eine Formel mit einem festen Bezug. Zum Beispiel:

=WENN('X:\mitarbeiter\ILK-Info\Beschaffung\[Lieferanten.xls]Lieferanten'!$A$4=""; ""; 'X:\mitarbeiter\ILK-Info\Beschaffung\[Lieferanten.xls]Lieferanten'!$A$4)

Kopiere die Formel: Markiere die Zelle mit der Formel und ziehe die Ecke nach unten und rechts, um die Formel auf die anderen Zellen zu kopieren. Beachte, dass der Bezug durch den $-Operator absolut bleibt.

Verwende ein Makro für mehrere Zellen: Bei vielen Zellen kann ein VBA-Makro hilfreich sein. Du kannst das folgende Makro verwenden, um die Formeln für ein ganzes Tabellenblatt zu ändern:

Sub Formel_korrigieren()
   Dim dblLz#, sSp$, zelle, Bereich$, sRow$
   Application.DisplayAlerts = False
   dblLz = ActiveCell.SpecialCells(xlLastCell).Row
   Bereich = "A1:M" & dblLz
   For Each zelle In Range(Bereich)
       sSp = Mid(zelle.Address, 2, 1)
       sRow = CStr(zelle.Row)
       zelle.FormulaLocal = "=Wenn('X:\mitarbeiter\ILK-Info\Beschaffung\[Lieferanten.xls]Lieferanten'!$" & sSp & "$" & sRow & _
       "="""";"""";'X:\mitarbeiter\ILK-Info\Beschaffung\[Lieferanten.xls]Lieferanten'!$" & sSp & "$" & sRow & ")"
   Next
   Application.DisplayAlerts = True
End Sub

Häufige Fehler und Lösungen

Fehler: Bezug bleibt nicht fest
Lösung: Stelle sicher, dass du $-Zeichen in deinen Formeln verwendest, um absolute Bezüge zu erstellen.
Fehler: Formeln werden beim Kopieren verändert
Lösung: Verwende die Funktion F4, um die Zellbezüge in absolute Bezüge zu ändern, bevor du die Formel kopierst.

Alternative Methoden

Mischbezüge verwenden: Nutze Mischbezüge wie $A1 oder A$1, um flexiblere Bezüge zu erstellen, die beim Kopieren teilweise relativ bleiben.
Verknüpfungen mit anderen Tabellenblättern: Du kannst auch Bezüge zu anderen Tabellenblättern innerhalb der gleichen Arbeitsmappe verwenden, um Daten zu konsolidieren.

Praktische Beispiele

Einfacher fester Bezug:
```
='X:\mitarbeiter\ILK-Info\Beschaffung\[Lieferanten.xls]Lieferanten'!$A$4
```
Dies bezieht sich immer auf die Zelle A4 in der angegebenen Datei.
Verwendung in mehreren Zellen: Wenn du die Formel in die Zellen B1 bis M1000 kopierst, bleibt der Bezug auf die Zelle A4 fest, wenn du $A$4 verwendest.

Tipps für Profis

Makros zur Automatisierung: Wenn du regelmäßig große Datenmengen bearbeitest, solltest du Makros in Betracht ziehen, um den Prozess der Bezugserstellung zu automatisieren.
Datenbankanwendungen: Überlege, ob es sinnvoll ist, deine Daten in einer Datenbank wie Access zu verwalten, besonders wenn du regelmäßig mit großen Datenmengen arbeitest.

FAQ: Häufige Fragen

1. Wie mache ich einen Bezug auf eine feste Zelle in einer anderen Datei?
Du kannst einen festen Bezug auf eine Zelle in einer anderen Datei erstellen, indem du den vollständigen Pfad zur Datei und die Zelle angibst, z.B.:

='X:\mitarbeiter\ILK-Info\Beschaffung\[Lieferanten.xls]Lieferanten'!$A$4

2. Kann ich mehrere Zellen gleichzeitig zu einem festen Bezug ändern?
Ja, du kannst VBA-Makros verwenden, um mehrere Zellen gleichzeitig zu ändern, was dir viel Zeit spart, insbesondere bei großen Datenmengen.

Beliebteste Forumthreads (12 Monate)

Feste Bezüge für gesamtes Tabellenblatt

Forumthread: Feste Bezüge für gesamtes Tabellenblatt

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Feste Bezüge für ein gesamtes Tabellenblatt in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Feste Bezüge für gesamtes Tabellenblatt

Forumthread: Feste Bezüge für gesamtes Tabellenblatt

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Feste Bezüge für ein gesamtes Tabellenblatt in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread