Live-Forum - Die aktuellen Beiträge
Datum
Titel
08.05.2024 23:37:20
Datenverbindung automatisiert aktualisieren
08.05.2024 21:22:24
In vorhandener Formel Jahreszahl mit einbeziehen
08.05.2024 21:15:37
Koordinaten im XY-Diagramm per Cursor ändern.
08.05.2024 19:10:09
doppelte Zeile gem. mehrerer Bedingungen löschen?
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1812to1816
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1812to1816
1812to1816
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel Zellenbezug verändern

Formel Zellenbezug verändern
22.02.2021 18:48:24
Sven

Hallo zusammen,
leider bin ich ein absoluter Excelanfänger und benötige Hilfe.
Folgende Situation:
Ich möchte von dem Tabellenblatt „G1“ Daten auf das Tabellenblatt „Tabelle1“ kopieren.
Hier seht ihr einen kleinen Auszug der Zellenbezeichungen der Werte, die aus dem Blatt G1 in das Blatt Tabelle kopiert werden sollen. Kann mir die Formeln dafür basteln? Ich bin wirklich nach 5 Stunden suchen im Netz ratlos.
B2 =G1! W1
B3=G1! AA1
B4=G1! AE1
C2=G1! W3
C3=G1! AA3
C4=G1! AE3
D2=G1! X3
D3=G1! AB3
D4=G1! AF3
E2=G1! Y3
E3=G1! AC3
E4=G1! AG3
Hier der Link zu der Excel Datei
https://www.herber.de/bbs/user/144155.xlsx
Danke im Voraus
Sven

34
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: im Prinzip genauso, wie Du schreibst ...
22.02.2021 18:54:56
neopa
Hallo Sven,
... also:
='G1'!W1 ='G1'!W3 ...
='G1'!AA1
...
Gruß Werner
.. , - ...
AW: im Prinzip genauso, wie Du schreibst ...
22.02.2021 19:08:02
Sven
Hallo Werner,
danke für die schnelle Antwort.
Mein Problem ist, wenn ich die Formeln in z. B. Spalte B nach unten ziehe, ändert sich der Zellbezug wie folgt.
Aus (B2 =G1!W1) wird (B3 =G1!W3) Ich benötige aber Folgendes (B3 =G1!AA1)
Also die Zeile bleibt gleich und die Spalte muss 3 nach rechts.
Gruss Sven
AW: dafür ...
22.02.2021 19:20:54
neopa
Hallo Swen,
... in B2: =INDEX('G1'!$1:$1;19+ZEILE(A1)*4) und nach unten kopieren.
Gruß Werner
.. , - ...
AW: dafür ...
22.02.2021 22:39:03
Luschi
Hallo Werner,
syntaktisch richtiger wäre aber doch wohl diese Formel:
=INDEX('G1'!$1:$1;;19+ZEILE(A1)*4)
also 19+ZEILE(A1)*4 auf der Spaltenposition der Index-Funktion.
Gruß von Luschi
aus klein-Paris
Anzeige
AW: kann man so sehen oder auch nicht ...
23.02.2021 08:27:18
neopa
Hallo Luschi,
... denn für eine Spaltenauswertung mit INDEX() schreiben wohl nur ganz wenige beispielsweise so:
=INDEX(A:A;ZEILE()*2;) oder gar =INDEX(A:A;ZEILE()*2;1)
sondern die meisten dafür einfach nur =INDEX(A:A;ZEILE(A1)*2)
Diese Schreibweisen sind nämlich mE sowohl von der Logik und somit auch vom Ergebnis her alle völlig korrekt.
Gruß Werner
.. , - ...
AW: kann man so sehen oder auch nicht ...
23.02.2021 08:44:46
Luschi
Guten Morgen Werner,
für einen Excel-Profi ist das sicher kein Problem, die Möglichkeiten von Excel so auszunutzen, aber für den Verstehen-Woller von Excelformeln kann es auch zu falschen Schlußfolgerungen führen; Deine Formel sieht bei mir inzwischen so aus:
=INDEX('G1'!$1:$1;;SPALTE(S1)+ZEILE(A1)*4)
Damit ist dann auch die Herkunft der zahl '19' geklärt.
Gruß von Luschi
aus klein-Paris
Anzeige
AW: das ist so aber inkonsequent ...
23.02.2021 08:59:33
neopa
Hallo Luschi,
... wenn schon so, dann solltest Du aber die Formel so schreiben:
=INDEX('G1'!$1:$1;1;SPALTE(W1)-4+ZEILE(A1)*4)
was dann auch die "Herkunft" der 19 in meiner Formel so ausführlich erklärt, wie ich es hier mit ... ;1; ... getan habe.
Gruß Werner
.. , - ...
AW: das ist so aber inkonsequent ...
23.02.2021 10:04:41
Daniel
Hi
Noch besser so:
=INDEX('G1'!$1:$1;1;SPALTE($W$1)+(ZEILE(A1)-1)*4)
Dann stehen alle Parameter (Startspalte, Versatzwert) direkt ablesbar und nur einmal in der Formel.
Gruß Daniel
AW: kannst es ja so schreiben, ...
23.02.2021 10:22:47
neopa
Hallo Daniel,
... wie ich aber bereits Luschi dargelegt hatte, halte ich die ..;1;... in der Formel weiterhin für völlig überflüssig. Die 19 durch SPALTE(W1) zu ersetzen, ist dagegen sinnvoll.
Gruß Werner
.. , - ...
Anzeige
AW: kannst es ja so schreiben, ...
23.02.2021 10:45:29
Daniel
Mag sein.
Aber die "-4" ist dann genauso überfüssig und sollte weg.
Die -1 ist hier ein feststehender Standardwert, der unabhängig von den anderen Parametern ist und sich niemals ändert.
Er bildet mit dem "Spalte(A1)" zusammen einen feststehenden Begriff.
Der Variable Abstand der Zellen (4) muss so nur einmalig in der Formel verwendet werden, das ist hier der großer Vorteil.
Stell dir mal deine und meine Formel vor, wenn die 4 kein fester Wert wäre, sondern ebenfalls wie die Spaltennummer über eine Teilformel berechnet werden müsste.
Gruß Daniel
AW: ich hatte keine -4 in meiner Formel owT
23.02.2021 10:57:04
neopa
Gruß Werner
.. , - ...
Anzeige
Dann entschuldige bitte.
23.02.2021 11:05:13
Daniel
Hi
sorry, ich meinte das hier:
Hallo Luschi,
... wenn schon so, dann solltest Du aber die Formel so schreiben:
=INDEX('G1'!$1:$1;1;SPALTE(W1)-4+ZEILE(A1)*4)
was dann auch die "Herkunft" der 19 in meiner Formel so ausführlich erklärt, wie ich es hier mit ... ;1; ... getan habe.
Gruß Werner
.. , - ...
ich wusste nicht, dass das nicht von dir ist, sorry, nochmal dafür.
Gruß Daniel
AW: der von Dir hier zitierte Beitrag ...
23.02.2021 11:26:00
Dir
Hallo,
... war meinerseits an Luschi gerichtet und ist im Zusammenhang mit dem diesem vorangegangenen Beiträgen nur für ihn gedacht gewesen.
Gruß Werner
.. , - ...
Anzeige
ich könnte das jetzt kommenieren
23.02.2021 11:39:09
Daniel
aber ich lass dass mal, weil es sinnlos ist.
Wenn sich jemand ganz konkret auf die Arbeit ...
24.02.2021 00:57:11
Luc:-?
…eines Anderen bezieht, sollte man schon alles lesen, wenn man das kommentieren, aber dabei keine Fehler machen will. Aber du hattest dich ja entschuldigt…
Luc :-?
AW: Wenn sich jemand ganz konkret auf die Arbeit ...
24.02.2021 11:13:24
Daniel
ja luc, fakt ist dass er -4 verwendet hat.
mache verleugnen sich halt lieber selbst, als zuzugeben dass ein anderer recht haben könnte.
AW: Fakt ist ...
24.02.2021 14:54:16
neopa
Hallo Daniel,
... ich hatte hier mit Luschi über etwas anderes diskutiert. In unseren Disput hast Du Dich mit einer Aussage eingemischt, die für sich genommen zwar nicht falsch war und ist - und auch von keinem behauptet wurde und wird - aber um das es Luschi und mir vordergründig gar nicht ging.
Offensichtlich war ich deswegen in meinem Betreff vom 23.02.2021 10:57:04 zu kurz angebunden und dazu auch etwas zu nachlässig. Für mich bezog sich meine dortige schnelle Aussage auf meine zuerst eingestellte Formel. Das dies anders gesehen werden kann und auch wird, habe ich mittlerweile mE schon längst klargestellt. Du hattest offensichtlich danach ja auch meinen ersten Beitrag hier im thread gelesen. Insofern hättest Du zumindest Deine Aussage im letzten Satz im Deinem hier vorangegangenen Beitrag bleiben lassen sollen.
Gruß Werner
.. , - ...
Anzeige
AW: Fakt ist ...
24.02.2021 15:29:38
Daniel
durch die Baumstruktur ist doch klar, auf welche Formel ich mich bezogen habe.
und da steht die -4 drin.
da muss man nichts dran rumdiskutieren.
dass du was anderes meinst, kann ich nicht wissen.
aber auch in deiner ersten Formel steckt die zuätzliche -4 implizit mit drin.
von daher ist es auch egal, welche Formel wir uns anschauen.
AW: ich hatte keine -4 in meiner Formel owT
23.02.2021 11:16:02
Luschi
Sorry Werner,
aber Du hattest meinen Vorschlag: =INDEX('G1'!$1:$1;;SPALTE(S1)+ZEILE(A1)*4)
gegen
=INDEX('G1'!$1:$1;1;SPALTE(W1)-4+ZEILE(A1)*4)
ausgetauscht, und da sehe ich ein '-4'
Gruß von Luschi
aus klein-Paris
Anzeige
AW: aber eben nicht in meiner 1. Formel owT
23.02.2021 11:37:47
neopa
Gruß Werner
.. , - ...
AW: aber eben nicht in meiner 1. Formel owT
23.02.2021 11:50:59
Daniel
du meintest diese hier:
=INDEX('G1'!$1:$1;19+ZEILE(A1)*4)
stellt sich die Frage, wie du auf 19 kommst.
kann nur so gehen: erste Zielspalte ist Spalte W = Spalte 23.
da man Zeile(A1)*4 nur mit 1 und nicht mit 0 beginnen lassen kann muss man also noch die 4 von den 23 abziehen, damit es passt.
ja die "-4" steht nicht explizit in der Formel drin, aber du hast sie implizit im Kopf berechnet. und das macht die Sache nicht besser, sondern schlechter.
man müsste das beim Anpassen der Formel an andere Tabellensituationen mit berücksichtigen, aber es ist nicht offensichtlich, dass man es tun muss und das birgt ein großes Fehlerrisiko beim Arbeiten mit berechnteten Zellbezügen nach diesem Schema.
in dem Fall wäre es besser zu schreiben: 23-4 statt 19, weil dann offensichtlich ist, das 23 die Startspalte und 4 der Spaltenabstand der einzelnen Gruppen ist und man die Formel dann ohne Kopfrechnen an andere Gegebenheiten anpassen kann.
Gruß Daniel
Anzeige
Dann wäre SPALTE(x23) wohl noch besser! owT
24.02.2021 01:01:27
Luc:-?
:-?
AW: Dann wäre SPALTE(x23) wohl noch besser! owT
24.02.2021 08:45:17
Daniel
Du drehst dich im Kreis Luc.
Darum gehts nicht, bzw bei dem Thema sind wir schon durch.
Lies bitte alle Beiträge vollständig, bevor du kommemtierst, sonst machst du Fehler.
Es geht hier um die Frage ob
A-B+X*B oder A+(X-1)*B
die günstigere Schreibweise ist.
Bzw ob es besser ist, einmal konstant -1 zu rechnen oder die Konstante B mehrfach in der Berechnung zu halten.
Ja, genau, deshalb schrieb ich das! :-] owT
24.02.2021 12:30:48
Luc:-?
:-?
omg owt
24.02.2021 13:04:28
Daniel
...
AW: Ja, genau, deshalb schrieb ich das! :-] owT
24.02.2021 14:04:55
Daniel
naja Luc, wenn du was sinnvolles hättest schreiben wollen, dann wärest du auf meine Frage eingegangen.
also nochmal, was würdest du empfehlen und warum?
(A und B sind die gegebenen Konstanten, X die Variable)
a) A-B+X*B
b) A+(X-1)*B
Gruß Daniel
Anzeige
Crossposting
22.02.2021 19:15:09
SF
Sagst du im anderen Forum wenigstens Bescheid dass es jetzt hier weitergeht?
Danke.
AW: Formel Zellenbezug verändern
22.02.2021 19:53:46
Sven
Formel funktioniert vielen Dank für die schnelle Hilfe.
Sven
AW: Formel Zellenbezug verändern
23.02.2021 20:55:34
Sven
Guten Abend,
ich bin am verzweifeln und bräuchte noch mal Hilfe.
Für die rot markierten Zellen in Tabelle1 benötige ich die Formeln.
Hier der Link
https://www.herber.de/bbs/user/144185.xlsx
Vielen Dank im Voraus
Sven
AW: da sind nur einfache Zellbezüge notwendig ...
24.02.2021 08:46:59
neopa
Hallo Sven,
... so:
Arbeitsblatt mit dem Namen 'Tabelle1'
 BCDE
6Grower 1 Raspberry2021
7Grower 1 Raspberry 
8Grower 1 Raspberry 
9    
10Grower 1702Raspberry2021

ZelleFormel
B6=B$10
D6=D$10
E6=E$10
B7=B$10
D7=D$10
B8=B$10
D8=D$10
B10='G1'!B1
C10='G1'!B7
D10='G1'!D11
E10='G1'!C7
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
AW: da sind nur einfache Zellbezüge notwendig ...
24.02.2021 12:38:33
Sven
Hallo Werner,
ich habe in Tabelle 1 nochmal genauer definiert und gelb markiert, welche Formeln ich benötige.
Zusätzlich habe ich die Tabellenblätter G2 bis G10. Diese sind alle genau gleich aufgebaut wie G1 und ich möchte diese auch in Tabelle 1 kopieren genau wie G1. Gibt es dafür eine einfache Lösung?
https://www.herber.de/bbs/user/144207.xlsx
Danke in Voraus.
Sven
AW: analog wie schon aufgezeigt ...
24.02.2021 14:36:05
neopa
Hallo Sven,
... in L2: =WENNFEHLER(INDEX('G1'!$11:$11;SPALTE(B1)+(ZEILE(L1)-1)*3);"")
und diese Formel nach rechts in M2 und dann beide nach unten ziehend kopieren.
Analog dann z.B. in O2:
=WENNFEHLER(INDEX('G1'!$13:$13;SPALTE(B1)+(ZEILE(L1)-1)*3);"")
Diese Formel kannst Du sogar bis Spalte Q kopieren.
Die anderen Spalten analog definierbar.
Gruß Werner
.. , - ...
AW: analog wie schon aufgezeigt ...
24.02.2021 21:53:32
Sven
Super jetzt bin ich einen großen Schritt weiter.
Vielen Dank Werner
Grüße Sven
AW: bitteschön owT
28.02.2021 11:24:46
neopa
Gruß Werner
.. , - ...

55 Forumthreads zu ähnlichen Themen

Per Makro zu einer bestimmten Zelle springen
Joachim

HI,
dank Eurer Hilfe weiss ich nun, wie ich meine Spalte prüfen kann:
For Each c In .Range("BD10:BD100")
If Not IsDate(c) And Not IsEmpty(c) Then
UserForm1.TextBox1 = "Cell " & c.Address & " is not a date !"
Exit Sub
End If
Next
Wenn es die Möglichkeit gi...
25.11.2008 08:16:21
Thread-ID: 1027553
5 Beiträge
Anzeige
Im Autofilter zu nächsten Zelle springen
Thomas

Hallo,
ich suche nach einer Möglichkeit in einer gefilterten Datei den Wert aus der nächsten Zeile zu übernehmen.
Hat jemand eine Lösung?
Danke
Thomas
25.11.2010 12:39:09
Thread-ID: 1188736
13 Beiträge
von zelle zu Zelle mit Eingabetaste springen
zelle

Hallo,
erstmal vielen dank an Hajo und Hr. Ehrenberger für die schnelle Hilfe bei meinen letzten Anfragen.
Nun habe ich schon wieder eine Frage. Wenn man ein Arbeitsblatt sperrt kann man ja in den freigegebenen Zellen mit Tab weiter springen. Kann man das auch so hinbekommen das man vo...
26.12.2010 18:20:27
Thread-ID: 1192854
13 Beiträge
Anzeige
von Zelle zu Zelle springen
Zelle

Hallo Herr Ehrensberger,
habe von Ihnen eine Datei endeckt mit der es möglich ist von Zelle zu Zelle zu springen.
Zellname ist input + fortlaufender Nummerrierung. Das funktioniert ja auch alles super. Ich habe aber in meiner Datei mehrere Tabellen. Was kann man da machen damit es auch...
28.12.2010 02:09:57
Thread-ID: 1192985
5 Beiträge
von Zelle zu Zelle springen
Zelle

Hallo Sepp,
ich muss noch mal nerven. Ich habe in meiner Datei mehrere Tabellen, die, wenn sie nicht benutzt werden, auf Visible = False gesetzt sind. Und genau da ist das Problem. Man kann ja jeden Name nur einmal vergeben und nicht in jeder Tabelle mit input1 anfangen. Wenn die Tabellen a...
29.12.2010 22:40:04
Thread-ID: 1193293
3 Beiträge
Zur Zelle mit Datum springen
JN

Hallo, ich benötige folgendes Makro:
In Spalte A befinden sich Datumswerte. In Zelle B1 geben ich ein Datum ein und möchte dann per Schaltfläche zum gleichen Datum springen (Zelle aktivieren), welches sich irgendwo in Spalte 1 befindet.
Danke und Grüße
JN
16.03.2011 13:10:10
Thread-ID: 1205966
3 Beiträge
Hyperlink - zu Zelle springen
Christoph

Hallo zusammen,
ich bitte um Unterstützung bei einem Hyperlink. Ich habe in B1 das aktuelle Datum. In A2 steht der Begriff "Heute" und in B2 soll der Hyperlink rein. Ich möchte, dass der Cursor beim Anklicken des links auf die Zelle aus A3:A100 springt, in der der Begriff "Heute" auftaucht....
30.05.2011 14:12:29
Thread-ID: 1216678
3 Beiträge
Formel oder Makro um Zelle nur mit Wert zu kopiere
Arthur

Hallo zusammen
Ich habe eine Formel in C31:
=(B31&" lbs."&"("&A31&" g"&")") die das Resultat: 11.5666 lbs. (0.0255 g) ergibt.
Jetzt bräuchte ich eine Formel, welche nur den Wert bei B20 einfügen würde oder auch ein Makro.
(rechtsklick-Kopieren-Einfügen spezia...
09.04.2012 23:28:04
Thread-ID: 1258550
2 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
4
2
12
12
2
4
2
2
2
1
2
6
18
2
3
2
1
2
5
1
6
5
3
13
8