Horizontal Dynamic Named Range

[Aviles]

Hi all,

I have created a dynamic range that expands vertically in the same column.

=OFFSET(B1,0,0,1,COUNTA(1:1))

My data starts and B1 (Sheet1) and currently ends in CU1, but I keep adding new data to the first row. This formula works well in Sheet1.

My issue is that I would like to move the drop-down box with this dynamic range to another sheet (Sheet2).

When I try do do this using the same formula =OFFSET(Sheet1!B1,0,0,1,COUNTA(1:1)), it says that "The Source currently evaluates to an error. Do you want to continue?"

Can anyone tell me what I'm doing wrong here please?

Thanks.

 

[jasonb75]

Have you tried qualifying the counta range with the sheet name as well?
COUNTA(Sheet1!1:1)
Simply using 1:1 will mean that it is looking at the sheet with the dropdown (sheet2) so if row 1 is empty then the count will be zero, which will cause an error.

 

[Aviles]

Have you tried qualifying the counta range with the sheet name as well?
COUNTA(Sheet1!1:1)
Simply using 1:1 will mean that it is looking at the sheet with the dropdown (sheet2) so if row 1 is empty then the count will be zero, which will cause an error.

Of course, thank you! That fixed it.