Dynamic named ranges

[already]

Hello

With this formula I can make a range dynamic for 1 column on the same sheet

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

What is the formula if I need to expand the range A1:D1 from another
worksheet in the same workbook?

Thanks in advance for your help

Al

 

[mikerickson]

Something like
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),4)

 

[already]

OK thanks but what with the range A1:D1

Kind regards

Al

 

[mikerickson]

The fifth argument of OFFSET specifies the number of colums, so
=OFFSET(Sheet2!$A$1, 0, 0, COUNTA(Sheet2!$A:$A), 4)
is the same as
=OFFSET(Sheet2!$A$1:$D$1, 0, 0, COUNTA(Sheet2!$A:$A), 4)

One issue might be if column A had fewer cells than columns B,C or D. In which case
MAX(COUNTA(Sheet2!$B:$B), COUNTA(Sheet2!$A:$A), COUNTA(Sheet2!$C:$C), COUNTA(Sheet2!$D:$D))
would be used for the row count argument.