If cell is blank, go to next right one.

[SinusxCosinusx]

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:

=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)))))))

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

 

[Peter_SSs]

also noticed by using this formula Excel overloads the PC for about a minute or two.

You are using whole column references so you are asking Excel to look at over 1 million rows (more than once) so there will be a significant drag on performance. In fact I got an "Excel ran out of resources .." message when I tried that formula structure.

This is how I would get the headers.

SinusxCosinusx.xlsm
	A	B
16	Notary	36
17	Translation	18.41
18	Transport 1	500
19	Hotel 1	260.4
20	Certificate	13
21	Coaching	23.69
22	PE 1	1280
23	PE 2	405
24	Certificate2	250
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
RBB

Cell Formulas
Range	Formula
A16:A42	A16	=IFERROR(INDEX(Tabelle3[[#Headers],[Notary]:[Certificate2]],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))),"")
B16:B42	B16	=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))),"")

 

[SinusxCosinusx]

You are using whole column references so you are asking Excel to look at over 1 million rows (more than once) so there will be a significant drag on performance. In fact I got an "Excel ran out of resources .." message when I tried that formula structure.

This is how I would get the headers.

SinusxCosinusx.xlsm
	A	B
16	Notary	36
17	Translation	18.41
18	Transport 1	500
19	Hotel 1	260.4
20	Certificate	13
21	Coaching	23.69
22	PE 1	1280
23	PE 2	405
24	Certificate2	250
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
RBB

Cell Formulas
Range	Formula
A16:A42	A16	=IFERROR(INDEX(Tabelle3[[#Headers],[Notary]:[Certificate2]],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))),"")
B16:B42	B16	=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))),"")

Hello Peter_SSs,

your solutions solved my case. Thank you very much for the formulas you combined. They work perfectly. ?

 

[Peter_SSs]

Cheers. Glad they worked for you. Thanks for the confirmation.