Short answer
Use ARRAY_CONSTRAIN(array,row_limit,column_limit)
Explanation
ARRAY_CONSTRAIN function could return the number of rows and columns that you want, just set the row_limit and column_limit parameters accordingly. To automatically set this parameters you could use a function like COUNT or COUNTA.
To calculate the number of rows/columns considering the data range by using built-in functions,
for rows use a formula like the following
=MAX(IF(LEN(A2:A)>0,ROW(A2:A)-1,0))
for columns use a formula like the following
=MAX(IF(LEN(1:1)>0,COLUMN(1:1)-1,0))
The sample formula in the question will look like the following:
=ARRAYFORMULA(
ARRAY_CONSTRAIN(
MMULT(N(ROW(A2:A)>=TRANSPOSE(ROW(A2:A))),SIGN(ROW(A2:A))),
MAX(IF(LEN(A2:A)>0,ROW(A2:A)-1,0)),
MAX(IF(LEN(1:1)>0,COLUMN(1:1)-1,0))
)
)
You should mark column AG in some way so it can be identified as the column that indirect needs. For example, by placing "Data" in the first row of that column.
Then you can use match to find the number of that column, which becomes a part of cell reference in R1C1 notation.
=indirect($A$1 & " Data!R3C" & match("Data", 1:1, 0), false)
This formula refers the cell in 3rd row of the column with header "Data", on the sheet specified in cell A1.
Best Answer
if you have such an option you can use Conditional Formatting to check for merged cells where you can immediately spot merged cells where the color pattern is interrupted like:
in the same manner you can use this formula for rows: