=INDIRECT("Sheet1.$B"&$X4&":$B"&$Y4) defines a range from the row number stored in X4 to the row number stored in Y4. You can probably achieve exactly the same using the OFFSET() function::
=OFFSET(sheet1.B1;X4-1;0;Y4-X4+1;1) might do the job. (Depending on what behaviour you need when inserting/deleting rows/columns, you may want a different starting point, and different displacement values. Hence, jkrideau's question on what you want to achieve is still valid.)

The INDIRECT() function uses string representations of cell references, which makes the addressing "hardcoded". OFFSET() uses a real reference as the starting point, so it will update to reflect changes in he worksheet.

=INDIRECT("Sheet1.$B"&$X4&":$B"&$Y4) defines a range from the row number stored in X4 to the row number stored in Y4. You can probably achieve exactly the same using the OFFSET() function::
=OFFSET(sheet1.B1;X4-1;0;Y4-X4+1;1) might do the job. (Depending on what behaviour you need when inserting/deleting rows/columns, you may want a different starting point, and different displacement values. Hence, jkrideau's question on what you want to achieve is still valid.)

The INDIRECT() function uses string representations of cell references, which makes the addressing "hardcoded". OFFSET() uses a real reference as the starting point, so it will update to reflect changes in he worksheet.