How one can create a dynamic range for extending formula with a variable number, without using vba? The problem is that, certain column (e.g. column A) is dedicated to inputs (1XN with dynamic N based on number of filled row), and formula are needed to be extended as a matrix (MXN with fixed M). Naively, solution seems to be for instance using =TRANSPOSE(A1:AX) where X is in fact number of filled rows in columns A: =COUNTA(A1:A1000).

How could that be achieved withouth vba?

CodePudding user response：

a quick solution would be =TRANSPOSE( OFFSET($A$1,,,COUNTA(A1:A1000),1) ) mind that for Office <365 instead of enter press ctrl enter (to make it an array formula)

but I would strongly advise to get a view of Dynamic Arrays in here or u_tb and see all the possibilities cheers