Formula for Auto Change 0 With Blank and Auto Short To the Top Table

Sample Here..

Originally Posted by PaulM100

in Sheet 2 B4: =IFERROR(INDEX('1. Manual'!$B$4:$B$13,MATCH(0,IF('1. Manual'!$B$4:$B$13<>"",COUNTIF('2. Auto Sunday'!$B$3:B3,'1. Manual'!$B$4:$B$13)),0)),"") - Array formula.

In Sheet2 AA4: do an index match wrapped in a IFERROR

At B4 Working like a charm
But i don't know what to do in A4? I am googling and searching, but still confuse to do an index match formula. Help? xD

Originally Posted by Bo_Ry

Please try at sheet Sunday
B3
=INDEX('1. Manual'!A3:I3,SHEET())

A4:B4 drag down

=IFERROR(INDEX('1. Manual'!$A:$F,AGGREGATE(15,6,ROW('1. Manual'!$A$4:$F$16)/(INDEX('1. Manual'!$A$4:$F$16,,MATCH($B$3,'1. Manual'!$A$3:$H$3,))<>""),ROWS(A$4:A4)),MATCH(A$3,'1. Manual'!$A$3:$H$3,)),"")

copy A1:D14 to the next sheet

At B3 i put =INDEX('1. Manual'!A3:I3,SHEET()) but it's error and said #NAME?
And the other when i drag, blank. Did i something wrong with this formula?

Screenshot_19.jpg

Originally Posted by PaulM100

in A4: =IFERROR(NDEX('1. Manual'!$A$4:$A$13,MATCH(B4,'1. Manual'!$B$4:$B$13,0))

Hmm. Not working on A4. I changed =IFERROR(NDEX( to =IFERROR(INDEX(
Screenshot_20.jpg

Originally Posted by Bo_Ry

AGGREGATE is available from Excel 2010, you have an older version.

Then B3 need to fill manually

A4 press Ctrl+Shift+Enter drag to B4 and drag down

=IFERROR(INDEX('1. Manual'!$A:$F,SMALL(IF((INDEX('1. Manual'!$A$4:$F$16,,MATCH($B$3,'1. Manual'!$A$3:$H$3,))<>""),ROW('1. Manual'!$A$4:$F$16)),ROWS(A$4:A4)),MATCH(A$3,'1. Manual'!$A$3:$H$3,)),"")

Okay, thank you now it's works ;D.

Originally Posted by Bo_Ry

AGGREGATE is available from Excel 2010, you have an older version.

Then B3 need to fill manually

A4 press Ctrl+Shift+Enter drag to B4 and drag down

=IFERROR(INDEX('1. Manual'!$A:$F,SMALL(IF((INDEX('1. Manual'!$A$4:$F$16,,MATCH($B$3,'1. Manual'!$A$3:$H$3,))<>""),ROW('1. Manual'!$A$4:$F$16)),ROWS(A$4:A4)),MATCH(A$3,'1. Manual'!$A$3:$H$3,)),"")

Aahh Sorry again, need quick help xD.
I try your Formula with new Data. And try formula in different angle. But it keeps me dizzy, i am so confuse.
Let's see my Picture

Screenshot_21.jpg
Screenshot_22.jpgScreenshot_23.jpg

How if the condition like that. Only keep line if theres some text in Coloum Time?

Originally Posted by Bo_Ry

Please try at A4 Press CSE drag to the right and down

=IFERROR(INDEX(Manual!A:A,SMALL(IF(Manual!$A$4:$A$23<>"",ROW(Manual!$A$4:$A$23)),ROWS(A$4:A4))),"")

Itttttssssssssssss PERFEEECTTTTT!!! You save my day twice! Thank you so much!
I have 50 Sheet and 250 Line. With this, all become simple.

All done.
Thank you again and again hehe ;D.