Zellbezug um eins hochzählen

Forumthread: Zellbezug um eins hochzählen

25.11.2004 15:36:52

Jürgen

Hallo zusammen,
ich habe ein kleines Zellbezugproblem und bin im vorhandenen Archiv nicht wirklich fündig geworden, wobei ich dachte, dass dieses Problem schon häufig aufgetreten sein müsste...
Folgender Sachverhalt:
Ich habe eine Rohdatenliste (siehe angehängte Datei), in die aus meinem Warenwirtschaftssystem automatisch Daten eingespielt werden. Diese stehen dann Zeilenweise untereinander.
Für sogenannte "Artikelkarteikarten" habe ich nun eine zweites Excelblatt ("formatierte Artikelliste"), welches auf das Rohdatenblatt zugreift und die Daten "schöner" anzeigt. Wenn ich nun meine erste Karteikarte (Zeile 4 bis 23) kopiere und darunter wieder einfüge (also dann ab Zeile 24), zählt Excel den relativen Bezug natürlich um 20 Zeilen nach oben. Klar.
Wie bekomme ich es nun hin, dass dieses hochzählen nur um den Faktor 1 erfolgt.
Beispiel: B45 soll statt dem Zellbezug (='Artikel Rohdaten'!C29 ist entstanden, weil ich die erste Karteikarte nach unten kopiert habe) den korrekten Bezug (='Artikel Rohdaten'!C10) ausweisen, da es ja die dritte Karteikarte ist und somit auf den "dritten" Artikel in den Rohdaten zurückgreifen soll.
Suchen und ersetzen ist schlecht, denn es handelt sich um mehrere Hundert Artikel, also ist eine Formel oder ein Makro eher angebracht.
So, das war jetzt zwar viel Text, aber ich hoffe es ist verständlich geworden ...
Besten Dank schonmal für Euer Bemühen.
Viele Grüße
Jürgen Stuhlträger
https://www.herber.de/bbs/user/13938.xls

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Zellbezug um eins hochzählen

25.11.2004 15:39:45

Jürgen

Hier nochmal die Datei OHNE Makros ...
Jürgen
https://www.herber.de/bbs/user/13939.xls

AW: Zellbezug um eins hochzählen

Hübi

Hi Jürgen,
ich habe deine Datei nach meinem Verständnis überarbeitet. Erklärung in Datei.
https://www.herber.de/bbs/user/13940.xls
Gruß Hübi

AW: Zellbezug um eins hochzählen

Hübi

Hi Jürgen,
sorry, noch eine kurz Korrektur

formatierte Artikelliste

	A	B
4
5	Bezeichnung	Artikel_Kurztext1
6
7	Suchwort	Suchwort1
8
9	Text	Bemerkung1
10
11
12
13
14	EK	10,00
15
16	Brutto-VK	20,00
17
18	Bemerkung	Bemerkung1
19
20	Artikel-Nr.	101
21
22	Lieferanten-Nr.	701
23

Formeln der Tabelle

B5 : =INDEX('Artikel Rohdaten'!$A$8:$H$17;AUFRUNDEN((ZEILE()-3)/20;0);3)
B7 : =INDEX('Artikel Rohdaten'!$A$8:$H$17;AUFRUNDEN((ZEILE()-3)/20;0);2)
B9 : =INDEX('Artikel Rohdaten'!$A$8:$H$17;AUFRUNDEN((ZEILE()-3)/20;0);5)
B14 : =INDEX('Artikel Rohdaten'!$A$8:$H$17;AUFRUNDEN((ZEILE()-3)/20;0);7)
B16 : =INDEX('Artikel Rohdaten'!$A$8:$H$17;AUFRUNDEN((ZEILE()-3)/20;0);8)
B18 : =INDEX('Artikel Rohdaten'!$A$8:$H$17;AUFRUNDEN((ZEILE()-3)/20;0);5)
B20 : =INDEX('Artikel Rohdaten'!$A$8:$H$17;AUFRUNDEN((ZEILE()-3)/20;0);1)
B22 : =INDEX('Artikel Rohdaten'!$A$8:$H$17;AUFRUNDEN((ZEILE()-3)/20;0);4)

Diagramm - Grafik - Excel Tabellen einfach im Web darstellen Excel Jeanie HTML 3.0 Download
Gruß Hübi

AW: Zellbezug um eins hochzählen

25.11.2004 17:49:28

Jürgen

Hallo Hübi,
besten Dank, das funktioniert ja einwandfrei!!! Du bist der Beste, klasse!!! Den Fehler habe ich selbst auch gemerkt (mit den Zeilen >20) und mir einfach mit Abrunden geholfen, da es ja nur die Lieferantenzeile betrifft ... habe eben erst deine korrektur gesehen ...
Also vielen herzlichen Dank, wieder ein glücklicher Mensch mehr auf der Welt ;-)
Bei mir ist nun noch ein weiteres Problem aufgetreten, vielleicht kannst Du mir da auch helfen, ich hab's aber auch nochmal als eigenen Beitrag gepostet:
Ich habe folgenden VBA-Code, der mir automatisch die Bilder in die Karteikarten lädt.
Sub Bild_Einfügen_und_Verkleinern_PräsiPreisliste() Dim bildname As String Dim Bereich As Range Dim zelle As Range For i = 5 To 1000 If Cells(i, 3) > 0 Then Variable = Cells(i, 3) Cells(i, 3).Select bildname = ActiveCell.Value Cells(i + 2, 4).Select ActiveSheet.Pictures.Insert(Cells(i, 3)).Select Selection.ShapeRange.LockAspectRatio = msoTrue Selection.ShapeRange.Height = 171.82 Selection.ShapeRange.Width = 114.54 End If Next i End Sub
Der Link wird aus der Zelle ausgelesen und das Bild an der entsprechenden Position eingelesen und formatiert. Funzt alles hervorragend! Jetzt habe ich leider festgestellt, dass in meinem System eine Menge "toter" links existieren, also es steht da zwar ein Link in dem Textfeld, aber dort ist kein Bild (mehr)! Folglich bricht das Makro ab und zeigt eine Fehlermeldung.
Ich benötige nun nach der Zeile "ActiveSheet.Pictures.Insert(Cells(i, 3)).Select" eine If Beziehung (oder so etwas in der Richtung), die dem Makro sagt, dass wenn kein Bild existiert einfach zur nächsten Zeile weitergegangen werden soll (bzw. goto next i).
Hast Du da eine Idee? Ich weiß den Befehl nicht für "kein Bild vorhanden" oder "Ziel existiert nicht" ...
Besten Dank.
Jürgen

AW: Zellbezug um eins hochzählen

Hübi

Hi Jügen,
es freut mich, dass es geklappt hat. Mit VBA kann ich dir leider nicht helfen, da bin ich selbst noch Eleve.
Stell doch das VBA-Problem neu ins Forum.
Gruß Hübi

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Öffne deine Excel-Datei mit der Rohdatenliste und der formatierte Artikelliste.
Wähle die Zelle aus, die du kopieren möchtest. Angenommen, du hast die Daten in den Zellen B5 bis B23.
Kopiere die Zellen (Strg + C) und füge sie an der gewünschten Stelle ein (z.B. ab B24).
Ändere die Formeln in den neuen Zellen so, dass sie auf den richtigen Bezug zugreifen. Nutze die INDEX-Funktion, um die Daten aus der Rohdatenliste korrekt zu beziehen. Beispiel:
```
=INDEX('Artikel Rohdaten'!$A$8:$H$17;AUFRUNDEN((ZEILE()-4)/20;0);3)
```
Hierbei passt die Formel den Bezug an, sodass beim Kopieren der Zellbezug nur um eins hochzählt.

Häufige Fehler und Lösungen

Fehler: Nach dem Kopieren wird der Zellbezug um mehrere Zeilen angepasst.
- Lösung: Verwende die INDEX-Funktion kombiniert mit AUFRUNDEN, um die richtige Zeile zu referenzieren.
Fehler: Die Zelle zeigt einen Fehler an (z.B. #BEZUG!).
- Lösung: Überprüfe die Referenzen und stelle sicher, dass die Daten in der Rohdatenliste vorhanden sind.

Alternative Methoden

Makros verwenden: Du kannst ein Makro erstellen, das automatisch die Zellbezüge anpasst. Hier ist ein einfaches Beispiel:

Sub ZellbezugAnpassen()
   Dim i As Integer
   For i = 5 To 1000
       Cells(i, 2).Formula = "=INDEX('Artikel Rohdaten'!$A$8:$H$17;AUFRUNDEN((ZEILE()-4)/20;0);3)"
   Next i
End Sub

Dies passt die Zellbezüge in der Spalte B automatisch an.

Suchen und Ersetzen: Für kleinere Datenmengen kann auch die Funktion „Suchen und Ersetzen“ nützlich sein, um Fehler manuell zu korrigieren.

Praktische Beispiele

Beispiel 1: Du hast Artikel in den Zellen B5 bis B23. Nach dem Kopieren in die Zellen B24 bis B44, möchtest du, dass die Formeln B24 bis B44 auf die Zeilen C10, C11, C12 usw. zugreifen.
- Verwende die angepasste INDEX-Formel in B24:
```
=INDEX('Artikel Rohdaten'!$A$8:$H$17;AUFRUNDEN((ZEILE()-24)/20;0);3)
```
Beispiel 2: Wenn du eine Liste von Bildern in deiner Artikelkarte hast, die auf Links basieren, stelle sicher, dass du die Fehlerbehandlung in deinem VBA-Code einbaust, um tote Links zu umgehen.

Tipps für Profis

Nutze Tastenkombinationen wie F4, um Zellbezüge schnell zu fixieren oder zu ändern.
Halte deine Daten in der Rohdatenliste gut organisiert, um die Verwendung von Formeln zu erleichtern.
Arbeite mit Tabelle (Excel 2010 und neuer) anstelle von normalen Bereichen, um die Handhabung der Daten zu vereinfachen.

FAQ: Häufige Fragen

1. Wie kann ich Zellbezüge beim Kopieren in Excel fixieren?
Du kannst die $-Zeichen verwenden, um Zellbezüge zu fixieren, z.B. ='Artikel Rohdaten'!$C$10.

2. Was mache ich, wenn ich viele Zellen gleichzeitig anpassen möchte?
Nutze die INDEX-Funktion zusammen mit AUFRUNDEN, um die Zellbezüge dynamisch zu gestalten, oder schreibe ein VBA-Makro zur Automatisierung.

Beliebteste Forumthreads (12 Monate)

Forumthread: Zellbezug um eins hochzählen

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Zellbezug in Excel um eins hochzählen

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)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread