I have two columns in excel; (1) A constant column which shall have the same text per row. (2) A dynamic column which updates based on another sheet (with a unique-formula).

I want column 1 to spill down the constant text in as many rows as the dynamic column has. If the dynamic column has 16 rows, then I want the constant to also have 16 rows (scenario 1). If the dynamic column updates to 12 rows, then I want the constant to automatically update to 12 rows (scenario 3).

I could call a "if(dynamic = "", "", text) in the constant, but that would not make the column dynamic. It would still contain the formula in row 13, 14, 15 even though the columns only have 12 rows (scenario 2).

Do anyone have a solution to my problem? Ideally I want the constant column to update automatically, without having "leftover-formulas" when the dynamic column updates, like in scenario 3.