If cell is blank, go to next right one.

S

SinusxCosinusx

New Member
Joined
Nov 22, 2013
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

I have a workbook with 4 sheets. In cell B16 of sheet "RBB" I want to create a formula to output the first value (from left to right) found between the columns L to AY of sheet "Belegerfassung" in the row related to the ID in column A. In sheet "RBB" this ID is reflected in cell E2.

Then the next cell B17 of sheet "RBB" shall output the next value (from left to right) found between the columns L to AY of sheet "Belegerfassung", as mentioned above. And so on, next cell B18... B19. The formula should ignore blank cells in the sheet "Belegerfassung", so it can show only from cells containing values. Also the next coming cells should not reproduce the same value the above cells already reproduced.

Same shall happen to the cells A16, A17, A18... in sheet "RBB". They should reproduce the respective column name from sheet "Belegerfassung" found in row 4.

I tested the below formula, but beside the fact that's too long, it's not effective:
Excel Formula: 
=IF(VLOOKUP($E2,Belegerfassung!$A:$AY,12,FALSE)="",VLOOKUP($E2,Belegerfassung!$A:$AY,13,FALSE),IF(VLOOKUP($E2,Belegerfassung!$A:$AY,13,FALSE)="",VLOOKUP($E2,Belegerfassung!$A:$AY,14,FALSE),IF(VLOOKUP($E2,Belegerfassung!$A:$AY,14,FALSE)="",VLOOKUP($E2,Belegerfassung!$A:$AY,15,FALSE),IF(VLOOKUP($E2,Belegerfassung!$A:$AY,15,FALSE)="",VLOOKUP($E2,Belegerfassung!$A:$AY,16,FALSE),IF(VLOOKUP($E2,Belegerfassung!$A:$AY,16,FALSE)="",VLOOKUP($E2,Belegerfassung!$A:$AY,17,FALSE),IF(VLOOKUP($E2,Belegerfassung!$A:$AY,17,FALSE)="",VLOOKUP($E2,Belegerfassung!$A:$AY,18,FALSE),VLOOKUP($E2,Belegerfassung!$A:$AY,12,FALSE)))))))

Pic01.JPG

Pic02.JPG


P.S.: My regional settings are set up for Germany, that's why I use semicolon instead of comma.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would also help if you gave us some smallish sample data to test with without having to type it out manually ..
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Peter_SSs said:
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would also help if you gave us some smallish sample data to test with without having to type it out manually ..
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Click to expand...
Hello Peter_SSs,
thank you for your notes. I updated my profile as suggested by you. I use O365. Following two minisheets ("RBB" and "Belegerfassung"):

TestFile.xlsx
ABCDE
1Finanzierungsvolumen und ZahlungsplanErstellungsdatum#NAME?
2TeilnehmernummerTR1130
3Status des Teilnehmers
4
5
6Persönliche AngabenRechnungsadresse
7
8NachnameSinoc/o
9Vorname(n)BlertaStraße und Nummer
10Postleitzahl
11Ort
12Land
13
14VorfinanzierungZahlungsplan
15
16Übersetzungskosten500,002021_0920,84
17Notarkosten2021_1020,84
18SARS-CoV-2-Test 12021_1120,84
19SARS-CoV-2-Test 22021_1220,84
20SARS-CoV-2-Test 32022_0120,84
21SARS-CoV-2-Test 42022_0220,84
22Sprachprüfung 12022_0320,84
23Sprachprüfung 22022_0420,84
24Fahrtkosten 12022_0520,84
25Fahrtkosten 22022_0620,84
26Fahrtkosten 32022_0720,84
27Fahrtkosten 42022_0820,84
28Fahrtkosten 52022_0920,84
29Fahrtkosten 62022_1020,84
30Fahrtkosten 72022_1120,84
31Fahrtkosten 82022_1220,84
32Fachkenntnisprüfung 12023_0120,84
33Fachkenntnisprüfung 22023_0220,84
34Hostelkosten 12023_0320,84
35Hostelkosten 2 2023_0420,84
36Hostelkosten 32023_0520,84
37Hostelkosten 42023_0620,84
38Urkunde2023_0720,84
39Nachhilfeunterricht 2023_0820,68
40Führungszeungnis OB2023_09 
41Antrag Gleichwertigkeitsprüfung2023_10 
42Adressermittlung 2023_11 
432023_12 
44Summe (EUR)500,002024_01 
452024_02 
46Eckdaten Rückzahlung2024_03 
472024_04 
48Monatsrate Rückzahlung (EUR)20,842024_05 
49Anzahl Raten242024_06 
50ZahlungsartDauerauftrag2024_07 
51Erster Rückzahlmonat2021_09
52Letzter Rückzahlmonat2023_08Summe (EUR)500,00
RBB
Cell Formulas
RangeFormula
E1E1=IF(LastSavedTimeStamp()="#Name?",TODAY(),FALSE)
B8B8=VLOOKUP($E$2,Tabelle3[[#All],[ID]:[Birthdate]],2,FALSE)
B9B9=VLOOKUP($E$2,Tabelle3[[#All],[ID]:[Birthdate]],4,FALSE)
D8D8=IF(VLOOKUP($E2,'[Third-party-costs.xlsx]Belegerfassung'!$A:$K,6,FALSE)="","Straße","c/o")
D9D9=IF($D$8="c/o","Straße und Nummer","Hausnummer & Zusatz")
B16B16=IF(VLOOKUP($E2,Belegerfassung!$A:$AY,12,FALSE)="",VLOOKUP($E2,Belegerfassung!$A:$AY,13,FALSE),IF(VLOOKUP($E2,Belegerfassung!$A:$AY,13,FALSE)="",VLOOKUP($E2,Belegerfassung!$A:$AY,14,FALSE),IF(VLOOKUP($E2,Belegerfassung!$A:$AY,14,FALSE)="",VLOOKUP($E2,Belegerfassung!$A:$AY,15,FALSE),IF(VLOOKUP($E2,Belegerfassung!$A:$AY,15,FALSE)="",VLOOKUP($E2,Belegerfassung!$A:$AY,16,FALSE),IF(VLOOKUP($E2,Belegerfassung!$A:$AY,16,FALSE)="",VLOOKUP($E2,Belegerfassung!$A:$AY,17,FALSE),IF(VLOOKUP($E2,Belegerfassung!$A:$AY,17,FALSE)="",VLOOKUP($E2,Belegerfassung!$A:$AY,18,FALSE),VLOOKUP($E2,Belegerfassung!$A:$AY,12,FALSE)))))))
E16E16=IF($B$44="nichts zu zahlen",0,IF($B$49=0,0,IF(AND($B$49>=1,$B$49<=35),$B$48,"zu viele Raten")))
D17:D50D17=DATE(YEAR(D16),MONTH(D16)+1,DAY(D16))
E17E17=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=1),"",IF($B$49=2,$B$44-SUM($E$16:$E$16),IF(AND($B$49>=3,$B$49<=35),$B$48,"zu viele Raten"))))
E18E18=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=2),"",IF($B$49=3,$B$44-SUM($E$16:$E$17),IF(AND($B$49>=4,$B$49<=35),$B$48,"zu viele Raten"))))
E19E19=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=3),"",IF($B$49=4,$B$44-SUM($E$16:$E$18),IF(AND($B$49>=5,$B$49<=35),$B$48,"zu viele Raten"))))
E20E20=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=4),"",IF($B$49=5,$B$44-SUM($E$16:$E$19),IF(AND($B$49>=6,$B$49<=35),$B$48,"zu viele Raten"))))
E21E21=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=5),"",IF($B$49=6,$B$44-SUM($E$16:$E$20),IF(AND($B$49>=7,$B$49<=35),$B$48,"zu viele Raten"))))
E22E22=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=6),"",IF($B$49=7,$B$44-SUM($E$16:$E$21),IF(AND($B$49>=8,$B$49<=35),$B$48,"zu viele Raten"))))
E23E23=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=7),"",IF($B$49=8,$B$44-SUM($E$16:$E$22),IF(AND($B$49>=9,$B$49<=35),$B$48,"zu viele Raten"))))
E24E24=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=8),"",IF($B$49=9,$B$44-SUM($E$16:$E$23),IF(AND($B$49>=10,$B$49<=35),$B$48,"zu viele Raten"))))
E25E25=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=9),"",IF($B$49=10,$B$44-SUM($E$16:$E$24),IF(AND($B$49>=11,$B$49<=35),$B$48,"zu viele Raten"))))
E26E26=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=10),"",IF($B$49=11,$B$44-SUM($E$16:$E$25),IF(AND($B$49>=12,$B$49<=35),$B$48,"zu viele Raten"))))
E27E27=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=11),"",IF($B$49=12,$B$44-SUM($E$16:$E$26),IF(AND($B$49>=13,$B$49<=35),$B$48,"zu viele Raten"))))
E28E28=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=12),"",IF($B$49=13,$B$44-SUM($E$16:$E$27),IF(AND($B$49>=14,$B$49<=35),$B$48,"zu viele Raten"))))
E29E29=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=13),"",IF($B$49=14,$B$44-SUM($E$16:$E$28),IF(AND($B$49>=15,$B$49<=35),$B$48,"zu viele Raten"))))
E30E30=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=14),"",IF($B$49=15,$B$44-SUM($E$16:$E$29),IF(AND($B$49>=16,$B$49<=35),$B$48,"zu viele Raten"))))
E31E31=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=15),"",IF($B$49=16,$B$44-SUM($E$16:$E$30),IF(AND($B$49>=17,$B$49<=35),$B$48,"zu viele Raten"))))
E32E32=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=16),"",IF($B$49=17,$B$44-SUM($E$16:$E$31),IF(AND($B$49>=18,$B$49<=35),$B$48,"zu viele Raten"))))
E33E33=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=17),"",IF($B$49=18,$B$44-SUM($E$16:$E$32),IF(AND($B$49>=19,$B$49<=35),$B$48,"zu viele Raten"))))
E34E34=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=18),"",IF($B$49=19,$B$44-SUM($E$16:$E$33),IF(AND($B$49>=20,$B$49<=35),$B$48,"zu viele Raten"))))
E35E35=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=19),"",IF($B$49=20,$B$44-SUM($E$16:$E$34),IF(AND($B$49>=21,$B$49<=35),$B$48,"zu viele Raten"))))
E36E36=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=20),"",IF($B$49=21,$B$44-SUM($E$16:$E$35),IF(AND($B$49>=22,$B$49<=35),$B$48,"zu viele Raten"))))
E37E37=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=21),"",IF($B$49=22,$B$44-SUM($E$16:$E$36),IF(AND($B$49>=23,$B$49<=35),$B$48,"zu viele Raten"))))
E38E38=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=22),"",IF($B$49=23,$B$44-SUM($E$16:$E$37),IF(AND($B$49>=24,$B$49<=35),$B$48,"zu viele Raten"))))
E39E39=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=23),"",IF($B$49=24,$B$44-SUM($E$16:$E$38),IF(AND($B$49>=25,$B$49<=35),$B$48,"zu viele Raten"))))
E40E40=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=24),"",IF($B$49=25,$B$44-SUM($E$16:$E$39),IF(AND($B$49>=26,$B$49<=35),$B$48,"zu viele Raten"))))
E41E41=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=25),"",IF($B$49=26,$B$44-SUM($E$16:$E$40),IF(AND($B$49>=27,$B$49<=35),$B$48,"zu viele Raten"))))
E42E42=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=26),"",IF($B$49=27,$B$44-SUM($E$16:$E$41),IF(AND($B$49>=28,$B$49<=35),$B$48,"zu viele Raten"))))
E43E43=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=27),"",IF($B$49=28,$B$44-SUM($E$16:$E$42),IF(AND($B$49>=29,$B$49<=35),$B$48,"zu viele Raten"))))
E44E44=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=28),"",IF($B$49=29,$B$44-SUM($E$16:$E$43),IF(AND($B$49>=30,$B$49<=35),$B$48,"zu viele Raten"))))
E45E45=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=29),"",IF($B$49=30,$B$44-SUM($E$16:$E$44),IF(AND($B$49>=31,$B$49<=35),$B$48,"zu viele Raten"))))
E46E46=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=30),"",IF($B$49=31,$B$44-SUM($E$16:$E$45),IF(AND($B$49>=32,$B$49<=35),$B$48,"zu viele Raten"))))
E47E47=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=31),"",IF($B$49=32,$B$44-SUM($E$16:$E$46),IF(AND($B$49>=33,$B$49<=35),$B$48,"zu viele Raten"))))
E48E48=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=32),"",IF($B$49=33,$B$44-SUM($E$16:$E$47),IF(AND($B$49>=34,$B$49<=35),$B$48,"zu viele Raten"))))
E49E49=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=33),"",IF($B$49=34,$B$44-SUM($E$16:$E$48),IF(AND($B$49>=35,$B$49<=35),$B$48,"zu viele Raten"))))
E50E50=IF($B$44="nichts zu zahlen","",IF(AND($B$49>=0,$B$49<=34),"",IF($B$49=35,$B$44-SUM($E$16:$E$49),IF(AND($B$49>=35,$B$49<=35),$B$48,"zu viele Raten"))))
B44B44=IF(SUM($B$16:$B$42)=0,"nichts zu zahlen",SUM($B$16:$B$42))
B48B48=IF($B$44="nichts zu zahlen","nichts zu zahlen",IF($B$49>35,"zu viele Raten",IF($B$49=0,"Ratenanzahl angeben",ROUNDUP($B$44/$B$49,2))))
B50B50=IF(B49<=4,"Überweisung","Dauerauftrag")
B51B51=$D$16
B52B52=IF(E17="",D16,IF(E18="",D17,IF(E19="",D18,IF(E20="",D19,IF(E21="",D20,IF(E22="",D21,IF(E23="",D22,IF(E24="",D23,IF(E25="",D24,IF(E26="",D25,IF(E27="",D26,IF(E28="",D27,IF(E29="",D28,IF(E30="",D29,IF(E31="",D30,IF(E32="",D31,IF(E33="",D32,IF(E34="",D33,IF(E35="",D34,IF(E36="",D35,IF(E37="",D36,IF(E38="",D37,IF(E39="",D38,IF(E40="",D39,IF(E41="",D40,IF(E42="",D41,IF(E43="",D42,IF(E44="",D43,IF(E45="",D44,IF(E46="",D45,IF(E47="",D46,IF(E48="",D47,IF(E49="",D48,IF(E50="",D49,D50))))))))))))))))))))))))))))))))))
E52E52=IF(SUM($E$16:$E$50)=0,"nichts zu zahlen",SUM($E$16:$E$50))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A42Expression=$B$42>0textNO
A41Expression=$B$41>0textNO
A40Expression=$B$40>0textNO
A39Expression=$B$39>0textNO
A38Expression=$B$38>0textNO
A37Expression=$B$37>0textNO
A36Expression=$B$36>0textNO
A35Expression=$B$35>0textNO
A34Expression=$B$34>0textNO
A33Expression=$B$33>0textNO
A32Expression=$B$32>0textNO
A31Expression=$B$31>0textNO
A30Expression=$B$30>0textNO
A29Expression=$B$29>0textNO
A28Expression=$B$28>0textNO
A27Expression=$B$27>0textNO
A26Expression=$B$26>0textNO
A25Expression=$B$25>0textNO
A24Expression=$B$24>0textNO
A23Expression=$B$23>0textNO
A22Expression=$B$22>0textNO
A21Expression=$B$21>0textNO
A20Expression=$B$20>0textNO
A19Expression=$B$19>0textNO
A18Expression=$B$18>0textNO
A17Expression=$B$17>0textNO
A16Expression=$B$16>0textNO
D1Expression=$E$1=""textNO
D3Expression=$E$3=""textNO
D2Expression=$E$2=""textNO
D50Expression=$E$50=""textNO
D49Expression=$E$49=""textNO
D48Expression=$E$48=""textNO
D47Expression=$E$47=""textNO
D46Expression=$E$46=""textNO
D45Expression=$E$45=""textNO
D44Expression=$E$44=""textNO
D43Expression=$E$43=""textNO
D42Expression=$E$42=""textNO
D41Expression=$E$41=""textNO
D40Expression=$E$40=""textNO
D39Expression=$E$39=""textNO
D38Expression=$E$38=""textNO
D37Expression=$E$37=""textNO
D36Expression=$E$36=""textNO
D35Expression=$E$35=""textNO
D34Expression=$E$34=""textNO
D33Expression=$E$33=""textNO
D32Expression=$E$32=""textNO
D31Expression=$E$31=""textNO
D30Expression=$E$30=""textNO
D29Expression=$E$29=""textNO
D28Expression=$E$28=""textNO
D27Expression=$E$27=""textNO
D26Expression=$E$26=""textNO
D25Expression=$E$25=""textNO
D24Expression=$E$24=""textNO
D23Expression=$E$23=""textNO
D22Expression=$E$22=""textNO
D21Expression=$E$21=""textNO
D20Expression=$E$20=""textNO
D19Expression=$E$19=""textNO
D18Expression=$E$18=""textNO
D16Expression=$E$16=0textNO
D17Expression=$E$17=""textYES
B8:B9Cellcontains a blank value textNO
E8:E12Cellcontains a blank value textNO
Cells with Data Validation
CellAllowCriteria
E2List=Belegerfassung!$A$5:$A$14


TestFile.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAY
1Data has to be entered from respetive department
2
3Candidate IDBasic DataAddressNotaryTranslationCorona TestFlight, Train and Other CostsHotel CostsGood ConductCoachingAddress SearchRecognitionLoanGerman CourseGerman ExcamOther CostsProffessional ExamCertifikate
4IDSurnameBirthnameFornameBirthdateCompanyStreetNo.ZIPPlaceCountryNotaryTranslationSARS-CoV-2Transport 1Transport 2Transport 3Transport 4Transport 5Transport 6Transport 7Transport 8Transport 9Hotel 1Hotel 2Hotel 3Hotel 4Hotel 5Hotel 6Hotel 7Hotel 8CertificateCoachingASRecognitionLoanCourse 1Course 2Course 3Course 4Course 5Exam 1Exam 2Exam 3Exam 4Other 1Other 2Other 3PE 1PE 2Certificate2
5TR1130SinoFaskoBlerta13.11.1985Institution ALangestr.1559555LippstadtDE36,00 €18,41 €500,00 €260,40 €13,00 €23,69 €1.280,00 €405,00 €250,00 €
6TR1713MetajRomario14.12.1986Agency BKurzestr. 2070565StuttgartDE45,00 €20,00 €60,00 €300,00 €157,00 €34,00 €12,00 €230,00 €141,00 €13,00 €20,00 €450,00 €450,00 €150,00 €1.280,00 €250,00 €
7EL0024PrengaLlesh15.01.1987Board CBreitestr. 2544369DortmundDE50,00 €15,00 €120,00 €345,00 €432,00 €567,00 €543,00 €321,00 €234,00 €334,00 €122,00 €26,00 €250,00 €250,00 €150,00 €150,00 €150,00 €150,00 €250,00 €
8
9
10
11
12
13
14
Belegerfassung
 
Upvote 0
Sorry, I initially posted an incorrect result as I forgot about L:AY columns only & deleted it.
Is this what you want? I am unsure as it seems to fit the written description but the values do not align with the labels in column A.
Adjust comma to semicolon as required.

SinusxCosinusx.xlsm
AB
14Vorfinanzierung
15
16Übersetzungskosten36
17Notarkosten18.41
18SARS-CoV-2-Test 1500
19SARS-CoV-2-Test 2260.4
20SARS-CoV-2-Test 313
21SARS-CoV-2-Test 423.69
22Sprachprüfung 11280
23Sprachprüfung 2405
24Fahrtkosten 1250
25Fahrtkosten 2
26Fahrtkosten 3
27Fahrtkosten 4
28Fahrtkosten 5
29Fahrtkosten 6
30Fahrtkosten 7
31Fahrtkosten 8
32Fachkenntnisprüfung 1
33Fachkenntnisprüfung 2
34Hostelkosten 1
35Hostelkosten 2
36Hostelkosten 3
37Hostelkosten 4
38Urkunde
39Nachhilfeunterricht
40Führungszeungnis OB
41Antrag Gleichwertigkeitsprüfung
42Adressermittlung
RBB
Cell Formulas
RangeFormula
B16:B24B16=LET(r,INDEX(Tabelle3[[Notary]:[Certificate2]],MATCH(E2,Tabelle3[ID],0),0),TRANSPOSE(FILTER(r,r<>"","")))
Dynamic array formulas.
 
Upvote 0
Peter_SSs said:
Sorry, I initially posted an incorrect result as I forgot about L:AY columns only & deleted it.
Is this what you want? I am unsure as it seems to fit the written description but the values do not align with the labels in column A.
Adjust comma to semicolon as required.

SinusxCosinusx.xlsm
AB
14Vorfinanzierung
15
16Übersetzungskosten36
17Notarkosten18.41
18SARS-CoV-2-Test 1500
19SARS-CoV-2-Test 2260.4
20SARS-CoV-2-Test 313
21SARS-CoV-2-Test 423.69
22Sprachprüfung 11280
23Sprachprüfung 2405
24Fahrtkosten 1250
25Fahrtkosten 2
26Fahrtkosten 3
27Fahrtkosten 4
28Fahrtkosten 5
29Fahrtkosten 6
30Fahrtkosten 7
31Fahrtkosten 8
32Fachkenntnisprüfung 1
33Fachkenntnisprüfung 2
34Hostelkosten 1
35Hostelkosten 2
36Hostelkosten 3
37Hostelkosten 4
38Urkunde
39Nachhilfeunterricht
40Führungszeungnis OB
41Antrag Gleichwertigkeitsprüfung
42Adressermittlung
RBB
Cell Formulas
RangeFormula
B16:B24B16=LET(r,INDEX(Tabelle3[[Notary]:[Certificate2]],MATCH(E2,Tabelle3[ID],0),0),TRANSPOSE(FILTER(r,r<>"","")))
Dynamic array formulas.
Click to expand...
This formula doesn't work for me. The "r" is not accepted as value or name. Is there another formula which might work also on older version of Excel like 2016 and 2019, as some colleagues work from home and do not have O365.
 
Upvote 0
SinusxCosinusx said:
Is there another formula which might work also on older version of Excel like 2016 and 2019,
Click to expand...
Before I embark on that, are the results shown in my mini-sheet what you want and where you want them?
 
Upvote 0
Peter_SSs said:
... are the results shown in my mini-sheet what you want and where you want them?
Click to expand...
Assuming they are, try this

SinusxCosinusx.xlsm
B
1636
1718.41
18500
19260.4
2013
2123.69
221280
23405
24250
25 
26 
27 
28 
29 
30 
31 
32 
33 
34 
35 
36 
37 
38 
39 
40 
41 
42 
RBB
Cell Formulas
RangeFormula
B16:B42B16=IFERROR(INDEX(Tabelle3[[Notary]:[Certificate2]],MATCH(E$2,Tabelle3[ID],0),AGGREGATE(15,6,(COLUMN(Tabelle3[[Notary]:[Certificate2]])-COLUMN(Tabelle3[Notary])+1)/(INDEX(Tabelle3[[Notary]:[Certificate2]],MATCH(E$2,Tabelle3[ID],0),0)<>""),ROWS(B$16:B16))),"")
 
Upvote 0
Peter_SSs said:
Assuming they are, try this

SinusxCosinusx.xlsm
B
1636
1718.41
18500
19260.4
2013
2123.69
221280
23405
24250
25 
26 
27 
28 
29 
30 
31 
32 
33 
34 
35 
36 
37 
38 
39 
40 
41 
42 
RBB
Cell Formulas
RangeFormula
B16:B42B16=IFERROR(INDEX(Tabelle3[[Notary]:[Certificate2]],MATCH(E$2,Tabelle3[ID],0),AGGREGATE(15,6,(COLUMN(Tabelle3[[Notary]:[Certificate2]])-COLUMN(Tabelle3[Notary])+1)/(INDEX(Tabelle3[[Notary]:[Certificate2]],MATCH(E$2,Tabelle3[ID],0),0)<>""),ROWS(B$16:B16))),"")
Click to expand...
Yes, the results in your mini-sheet are correct. But it does not work on my sheet, it comes up with #NAME?. Can I send you the original file?
 
Upvote 0
SinusxCosinusx said:
Yes, the results in your mini-sheet are correct. But it does not work on my sheet, it comes up with #NAME?. Can I send you the original file?
Click to expand...
It worked. I had to adjust a few parts and it worked as expected. Thank you very much. :biggrin:
So how shall I adapt the formula to show the respective column name of sheet "Belegerfassung" on the cells A16 to A42 of sheet "RBB"?
 
Upvote 0
SinusxCosinusx said:
It worked. I had to adjust a few parts and it worked as expected. Thank you very much. :biggrin:
So how shall I adapt the formula to show the respective column name of sheet "Belegerfassung" on the cells A16 to A42 of sheet "RBB"?
Click to expand...
I used the below formula to retrieve the column header to the respective value, but somehow the first cell A16 doesn't show up the right header. It shows the header of column AU, which actually is empty. The other cells A17 to A42 do show the right column header.
Excel Formula: 
=INDEX(Belegerfassung!$L$4:$AY$4;SUMPRODUCT(MAX((Belegerfassung!$L:$AY=$B16)*(COLUMN(Belegerfassung!$L:$AY))))-COLUMN(Belegerfassung!$L$4)+1)

I also noticed by using this formula Excel overloads the PC for about a minute or two. The processor goes up to 100 % and the file itself gets a size over 5 GB of RAM when viewed in the Task Manager. Does this happen because of the large data amount in the database? ?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
VBA Code - Create Log sheet (Errorlog) in case VBA Macro condition is not met
Replies
1
Views
420
Tupe77
T
J
  • Question Question
Vlookup into next blank column
Replies
4
Views
347
Vogateer
V
B
  • Question Question
VBA Code to Send Mail to skip blank cells
Replies
1
Views
274
Micron
M
D
  • Question Question
VLOOKUP using two criteria
Replies
3
Views
633
etaf
etaf
R
  • Question Question
VBA for Cut, move, paste, delete rows
Replies
6
Views
150
RForster6
R

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top