Google Sheets - Set row number based from another cell value, inside the formula?

Question

I would like to set the value of the row, from another cell

For example, this H186 cell, if I put a value in the cell A1, like 200, then the value would be H200

Something like this: Assuming that A1 cell has a different value

=arrayformula(B2:B186&H2:H186) ===> =arrayformula(B2:B186&H2:H(A1))

Thanks in advance

Answer 1

You can create a string first in another cell and pass this cell from a dynamic range( Data > Named ranges in Menu).

example: you have Column A random numbers, in Column B you can pass any value in B1. In B2 string value and the formula cell, indirect function.