Auflistung von Namen in einer Tabelle nach Vorgabe aus anderer Zelle

[Daniel Albert]

Guten MOrgen,

eine Frage habe ich noch. Da ich ja für jeden Monat eine Tabelle für die Abrechnung der Essenskosten aller Kinder erstelle wäre es blöd, dass in einer alten Liste von Januar auf einmal neue Kinder aus September vorhanden sind. Aktuell sind die tabellen ja mit der aktiven Mitglieder Liste verbunden. Gibt es einen Befehl um per Makro alle Daten aus Verknüpfungen in feste Werte umzuwandeln das statt

Code:

=WENNFEHLER(INDEX('aktive Mitglieder'!$B$4:$B$63;KKLEINSTE(WENN('aktive Mitglieder'!$H$4:$H$63="A";ZEILE($1:$60));ZEILE(A1)));"")

Der Wert z.B Arnold ... dort aufgelistet wird?

Gruß Daniel

[Rabe]

Hi Daniel,

Gibt es einen Befehl um per Makro alle Daten aus Verknüpfungen in feste Werte umzuwandeln das statt

Code:

=WENNFEHLER(INDEX('aktive Mitglieder'!$B$4:$B$63;KKLEINSTE(WENN('aktive Mitglieder'!$H$4:$H$63="A";ZEILE($1:$60));ZEILE(A1)));"")

Der Wert z.B Arnold ... dort aufgelistet wird?

Du könntest es mit einem der beiden Makros machen (Makro 1 ist nur aufgezeichnet), dann sind die Formeln in diesen Zellen aber weg:

Option Explicit

Sub Umwandeln_in_feste_Werte_1()
' 
' Umwandeln_in_feste_Werte Makro 
' Dieses Makro macht aus Formeln feste Werte. 
' 

' 
   Range("A13").Select
   Selection.Copy
   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
   
' kürzer 
'    Range("A13").Copy 
'    Range("A13").PasteSpecial Paste:=xlPasteValues 
'    Application.CutCopyMode = False 
End Sub

Sub Umwandeln_in_feste_Werte_2()
' 
' Umwandeln_in_feste_Werte Makro 
' Dieses Makro macht aus Formeln feste Werte. 
' 

' 
   Range("A14").Value = Range("A14").Value
   
' kürzer 
'   Range("A14") = Range("A14") 
   
End Sub

VBA/HTML - CodeConverter für Office-Foren, AddIn für Excel/Word 2000-2013 - komplett in VBA geschrieben von Lukas Mosimann. Projektbetreuung durch mumpel
Code erstellt und getestet in Office 15

und das ".Value" kannst Du auch noch weglassen.

[Winny]

Hi

hier zum Ersetzen und Setzen der Formeln, passend für dein gepostetes Beispiel

Code:

Sub SetzeFormeln()
   With Worksheets("Mai 2015")
       .Range("A5:A5").FormulaArray = "=IFERROR(INDEX('aktive Mitglieder'!$B$4:$B$63,SMALL(IF('aktive Mitglieder'!$H$4:$H$63=""A"",ROW($1:$60)),ROW(A1))),"""")"
       .Range("A5:A54").FillDown
       
       .Range("A62").FormulaArray = "=IFERROR(INDEX('aktive Mitglieder'!$B$4:$B$63,SMALL(IF(('aktive Mitglieder'!$H$4:$H$63=""A"")*(TEXT('aktive Mitglieder'!$I$4:$I$63,""hh:mm """"Uhr"""""")=""12:30 Uhr""),ROW($1:$60)),ROW(A1))),"""")"
       .Range("A62:A111").FillDown
       
       .Range("A117").FormulaArray = "=IFERROR(INDEX('aktive Mitglieder'!$B$4:$B$63,SMALL(IF(('aktive Mitglieder'!$H$4:$H$63=""A"")*(TEXT('aktive Mitglieder'!$I$4:$I$63,""hh:mm """"Uhr"""""")=""13:00 Uhr""),ROW($1:$60)),ROW(A1))),"""")"
       .Range("A117:A166").FillDown
       
       .Range("A172").FormulaArray = "=IFERROR(INDEX('aktive Mitglieder'!$B$4:$B$63,SMALL(IF(('aktive Mitglieder'!$H$4:$H$63=""A"")*(TEXT('aktive Mitglieder'!$J$4:$J$63,""hh:mm """"Uhr"""""")=""12:30 Uhr""),ROW($1:$60)),ROW(A1))),"""")"
       .Range("A172:A221").FillDown
       
       .Range("A227").FormulaArray = "=IFERROR(INDEX('aktive Mitglieder'!$B$4:$B$63,SMALL(IF(('aktive Mitglieder'!$H$4:$H$63=""A"")*(TEXT('aktive Mitglieder'!$J$4:$J$63,""hh:mm """"Uhr"""""")=""13:00 Uhr""),ROW($1:$60)),ROW(A1))),"""")"
       .Range("A227:A276").FillDown
   End With
End Sub

Sub ErsetzeFormeln()
   With Worksheets("Mai 2015")
       .Range("A5:A54").Value = .Range("A5:A54").Value
       .Range("A62:A111").Value = .Range("A62:A111").Value
       .Range("A117:A166").Value = .Range("A117:A166").Value
       .Range("A172:A221").Value = .Range("A172:A221").Value
       .Range("A227:A276").Value = .Range("A227:A276").Value
   End With
End Sub

[Winny]

und das ".Value" kannst Du auch noch weglassen.

ja man kann, man sollte aber nicht, es ist mit .Value einfach 'sauberer' programmiert

[Rabe]

Hi,

in AH steht ja ein Bezugsfehler, was sollte da statt #BEZUG! in der Formel stehen?

[Rabe]

Hi Winny,

ja man kann, man sollte aber nicht, es ist mit .Value einfach 'sauberer' programmiert

ja, klar, drum habe ich ja auch beides drin.

[Daniel Albert]

Hallo,

Danke funktioniert. Habe es wie von euch beschrieben gemacht, einfach Makro - Aufzeichnung genutzt und dann das was man nicht braucht entfernt. Dachte nur es gäbe einen einfachen Befehl der komplett auf einem Tabellelblatt alle Formeln gegen die Werte tauscht.

Danke euch. jetzt passt die Tabelle und ich bin zufrieden. :18:

[Daniel Albert]

Ralf,

der Bezug war noch aus anderen Tabellenblätter aber für dieses Problem nicht wichtig. Danke

[Daniel Albert]

Guten Morgen,


musste meine tabelle etwas umstellen sodass die suchmuster für die Monatstabellen anders erstellt werden mussten. Habe jetzt in der tabelle "aktive Mitglieder" nicht nach Uhrzeit sondern nach Auswahl über ein X festgelegt da es auch Kinder gibt die auf die Woche verteilt einmal 12:30 oder 13:00 Uhr essen. Somit müssen diese auch in der jeweiligen Gruppe bei beiden Tabellen 12:30 und 13:00 Uhr erscheinen. Wollte die Formeln von euch selbst umstellen aber bekomme das mal wieder nicht gebacken.

Könnte mir da bitte jemand Helfen ?

Muster ist angefügt

[Winny]

Hi



ich hab dir die Formeln geändert, schau auch auf die Formeln in Modul2 und den zusätzlichen Button Formeln