Data Validation using a dynamic range on a different sheet

Hi Everyone,

How can I use data validation from a list using a dynamic range on another sheet? I set my range as:

=OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),1)

When I try to use this list on another sheet by typing =DynamicRange into the data validation box, where DynamicRange is the name of the list, I get the error "The Source currently evaluates to an error. Do you want to continue?” If I click yes my list is blank. When I go back to look at the list using the Name Manager the range formula has changed and now depends on which cell is currently selected. It now looks something like

=OFFSET(Sheet2!D11,0,0,COUNTA(Sheet2!D:D),1)

Where D11 is the currently selected cell.

Does anyone know what I am doing wrong here?

Thanks in advance,

Ger

Oh yeh! Thank you. On a similar note if my dynamic range has 2 columns, how can I select just one for my list?

Brilliant. Thanks very much

Geraint