I have a spreadsheet where from day to day there will be a varing number of rows containing data on sheet 1.

I would like a way to automatically have the data from column B on sheet 1 displayed on column L on sheet 2 without having to manually enter =Sheet1!B2 & dragging the formula down as I won't know without checking every time how many rows there are to do this for. Is there a way of doing this without macros?

Cheers in advance.