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.
<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
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.
<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