Sverweis mit Variablen
Archie
hänge schon seit 2 Tagen an einer für meine Begriffe leichten, aber doch
nicht zu realisierenden Problematik fest:
in einer sverweis-Funktion wie folgt
=WENN(ISTLEER(SVERWEIS(A27;'element
color'!$A$5:$BZ$76;$C$6;FALSCH));"";SVERWEIS(A27;'element
color'!$A$5:$BZ$76;$C$6;FALSCH))
möchte ich Variable einsetzen, damit mein Programm noch vielseitiger wird.
Der Begriff 'element color', der das gleichnamig sheet bezeichnet, soll in
einem Listfeld gewählt werden.
Angenommen, das Listfeld hinterlegt den Begriff in der Zelle B1, schaffe ich
es trotzdem nicht, in der Funktion den Begriff 'element color' einfach durch
B1 zu ersetzen.
Was mache ich verkehrt?
Vielen Dank im voraus
AJablonski
Monika Weber
> hänge schon seit 2 Tagen an einer für meine Begriffe leichten, aber doch
> nicht zu realisierenden Problematik fest:
>
> in einer sverweis-Funktion wie folgt
>
> =WENN(ISTLEER(SVERWEIS(A27;'element
> color'!$A$5:$BZ$76;$C$6;FALSCH));"";SVERWEIS(A27;'element
> color'!$A$5:$BZ$76;$C$6;FALSCH))
>
> möchte ich Variable einsetzen, damit mein Programm noch vielseitiger wird.
>
> Der Begriff 'element color', der das gleichnamig sheet bezeichnet, soll in
> einem Listfeld gewählt werden.
>
> Angenommen, das Listfeld hinterlegt den Begriff in der Zelle B1, schaffe
ich
> es trotzdem nicht, in der Funktion den Begriff 'element color' einfach
durch
> B1 zu ersetzen.
Du könntest dir eine VBA-Function stricken:
Function sv()
Dim TabName As String
TabName = [B1].Value
Application.Volatile
sv = IIf(WorksheetFunction.VLookup([a27], Worksheets(TabName) _
.Range("a5:c76"), 3, False) = "", "", (WorksheetFunction. _
VLookup([a27], Worksheets(TabName).Range("a5:c76"), 3, False)))
End Function
Nun musst du in die gewünschte Zelle nur noch =sv()
eingeben.
--
Ich hoffe, dass dir das weiterhilft.
Es liebs Grüessli aus der Schweiz
Monika Weber
------------------------------------------
Microsoft MVP für Excel
http://www.jumper.ch
http://www.jumper.ch/CODEBOOK.htm
Thomas Ramel
Archie schrieb:
>
> in einer sverweis-Funktion wie folgt
>
> =WENN(ISTLEER(SVERWEIS(A27;'element
> color'!$A$5:$BZ$76;$C$6;FALSCH));"";SVERWEIS(A27;'element
> color'!$A$5:$BZ$76;$C$6;FALSCH))
>
> möchte ich Variable einsetzen, damit mein Programm noch vielseitiger wird.
>
> Der Begriff 'element color', der das gleichnamig sheet bezeichnet, soll in
> einem Listfeld gewählt werden.
>
> Angenommen, das Listfeld hinterlegt den Begriff in der Zelle B1, schaffe ich
> es trotzdem nicht, in der Funktion den Begriff 'element color' einfach durch
> B1 zu ersetzen.
Verwende die Funktion INDIREKT(), um den Tabellennamen in B1
auszuwählen.
Hier ein Beispiel, dessen Bereiche Du anpasen kannst:
=SVERWEIS(A5;INDIREKT($B$1&"!A1:B10");2)
--
Mit freundlichen Grüssen
Thomas Ramel
- MVP für Microsoft-Excel -
Daniel Schmitz
>Verwende die Funktion INDIREKT(), um den Tabellennamen in B1
>auszuwählen.
>Hier ein Beispiel, dessen Bereiche Du anpasen kannst:
>
>=SVERWEIS(A5;INDIREKT($B$1&"!A1:B10");2)
das sehe ich genauso allerdings funktioniert die Formel bei mir
nur wenn man 2x Indirekt verwendet. D.h.
=SVERWEIS(A5;INDIREKT(INDIREKT($B$1&"!A1:B10"));2)
da erst durch diese Verknüpfung der richtige Zellbereich angesprochen
werden kann. Auf jeden Fall bei XL97. Oder sehe ich da was falsch?
Deine Variante liefert mir als Ergebnis immer "#Wert" und bei meiner
erscheint das gewünscht Ergebnis. Ist das Zufall oder nicht.... ?
Das ist eine ernstgemeinte Frage, da ich den Sinn auch nicht so ganz
verstehe.
Gruss
Daniel
sepp.burch
"Daniel Schmitz" <DaSc...@lycos.de> schrieb im Newsbeitrag
news:ae6r18$ruq$1...@news01.khis.de...
> Hi Thomas,
>
> >=SVERWEIS(A5;INDIREKT($B$1&"!A1:B10");2)
>
> das sehe ich genauso allerdings funktioniert die Formel bei mir
> nur wenn man 2x Indirekt verwendet. D.h.
>
> =SVERWEIS(A5;INDIREKT(INDIREKT($B$1&"!A1:B10"));2)
>
> da erst durch diese Verknüpfung der richtige Zellbereich angesprochen
> werden kann. Auf jeden Fall bei XL97. Oder sehe ich da was falsch?
> Deine Variante liefert mir als Ergebnis immer "#Wert" und bei meiner
> erscheint das gewünscht Ergebnis. Ist das Zufall oder nicht.... ?
> Das ist eine ernstgemeinte Frage, da ich den Sinn auch nicht so ganz
> verstehe.
Hallo Daniel
Ich habe die Formel von Thomas getestet auch mit XlL 97
bei mir bringt sie das richtige Ergebniss. Es muss wohl an etwas anderem
liegen.
MfG Josef
Daniel Schmitz
Das ist wenn man nicht tippen kann....
Ich habe nämlich anstelle von
INDIREKT($B$1&"!A1:B10")
INDIREKT($B$1) &"!A1:B10" eingesetzt und schon klappt´s
so nicht mehr....
was eine klammer doch so alles ausmachen kann... :-)
Da schäm ich mich doch jetzt ein wenig aber was soll´s
Danke für die Antwort!!
Gruss
Daniel
Thomas Ramel
Daniel Schmitz schrieb:
>
> >=SVERWEIS(A5;INDIREKT($B$1&"!A1:B10");2)
>
> das sehe ich genauso allerdings funktioniert die Formel bei mir
> nur wenn man 2x Indirekt verwendet. D.h.
>
> =SVERWEIS(A5;INDIREKT(INDIREKT($B$1&"!A1:B10"));2)
>
> da erst durch diese Verknüpfung der richtige Zellbereich angesprochen
> werden kann. Auf jeden Fall bei XL97. Oder sehe ich da was falsch?
> Deine Variante liefert mir als Ergebnis immer "#Wert" und bei meiner
> erscheint das gewünscht Ergebnis. Ist das Zufall oder nicht.... ?
> Das ist eine ernstgemeinte Frage, da ich den Sinn auch nicht so ganz
> verstehe.
Hmmm, ich habe die Formel in xl2000 'live' ausprobiert und deshalb ein
Komifeld mit 2 Tabellennamen angelegt und dieses mit B1 verknüpft.
In den Tabellen 2 und 3 habe ich dann unterschiedliche Werte
untergebracht.
Was steht bei Dir in B1?
Bei mir einfach ein Text: "Tabelle2" oder "Tabelle3"
Die '!' und der Zellbezug auf diese Tabellen dann per &-Verknüpfung
angehängt.
INDIREKT() benötigt einen String zur Auswertung; ob der komplett in der
Zelle steht oder mittels Verknüpfung fertig erzeugt wird ist eigentlich
egal.
PS: Ich bin froh, dass Sepp dasselbe Ergebnis wie ich erhalten hat ;-)
Thomas Ramel
Daniel Schmitz schrieb:
>
> Das ist wenn man nicht tippen kann....
> Ich habe nämlich anstelle von
> INDIREKT($B$1&"!A1:B10")
>
> INDIREKT($B$1) &"!A1:B10" eingesetzt und schon klappt´s
> so nicht mehr....
>
> was eine klammer doch so alles ausmachen kann... :-)
Mein Beitrag war schon weg, als ich diesen hier empfangen habe.
Ja; der String gehört komplett _in_ die Klammern der
INDIREKT()-Funktion.
Da ich nicht gerne abtippe (schreibfaul?) habe ich mir angewöhnt, die
Formeln direkt aus dem Mail-Client zu kopieren und in Excel einzufügen
(und vice versa).
Archie
Vielen Dank.
Ich habe es so lange nicht hinbekommen, bis ich die Tabellen mit
"Einwortbezeichner", also ohne Leerzeichen bezeichnet habe.
Dieses Leerzeichen war Schuld daran, dass Excel automatisch die Tabelle in
Hochkommata ' gesetzt hat und genau das zu übergeben war mir nicht möglich.
> Angenommen Dein ausgewählter Wert steht in Zelle A1 (= verknüpfte Zelle
des
> Listenfeldes bzw. Feld mit Datengültigkeitsliste), dann lautet die gesamte
> Formel wie folgt (vorausgesetzt, du hast in A1 wirklich auch die
> Anführungszeichen, sonst müsstest du diese in der Formel ergänzen):
>
> =SVERWEIS(A27;INDIREKT($A$1 & "!$A$5:$BZ$76");$C$6;FALSCH)
Vielen Dank
Archie