multiple tables to 1 list with flexible rows

[sukin]

Hi all,

I am working on a new sheet combining multiple tables from one sheet into 1 long list.

Only the length of the tables can be variable and dynamic.

Currently, I made a work around to code lines in use and empty/last lines.

Below is an example of the coded lines I already made.

Corresponds with row no. A B C D E 6 1 1 1 1 1 1 7 1 1 1 1 1 2 8 1 1 1 1 1 3 9 1 1 1 1 1 4 10 1 1 1 1 1 5 11 1 1 1 1 1 6 12 1 1 1 1 1 7 13 1 1 1 1 1 <colgroup><col><col><col span="5"></colgroup><tbody> </tbody>

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

Now, all the ones need to be listed in a single list.

So far, when column A --> the ones end my list start with the 1's in column B.

The problem is when column A for example expands of shrinks with filled lines I need the flexibility to keep starting the first line of column B after the last line of column A.
Now, a VBA solution would be the best way I guess, but is there also an option to just use a combo of regular formulas?

I am this far:

IF($C6=1,$C$5,IF(INDIRECT("$D"&IF($C6<>1,$B$6+$A7,1))=1,$D$5,IF(INDIRECT("$e"&IF(AND($C6<>1,$D6<>1),$B$6+$A7,1))=1,$E$5,IF(INDIRECT("$f"&IF(AND($C6<>1,$D6<>1,$E6<>1),$B$6+$A7,1))=1,$F$5,IF(INDIRECT("$g"&IF(AND($C6<>1,$D6<>1,$E6<>1,$F6<>1),$B$6+$A7,1))=1,$G$5,0)))))

I need something to make the $A7 part flexible

kind regards,

Suk In

 

[sandy666]

post a link to shared excel file with representative example source and expecting result.
you can use google drive, one drive or any similar service

edit:
what is your Excel (Office) version. eg. 2007, 2016, 2019 or eny other.

 

[sukin]

Hi,

below is the link to a test file of mine.

https://1drv.ms/x/s!AnPMESFKI9iwgZtt9eFP0wLd5Wlc1g

I am using excel for office 365, so I guess it's like 2016.