#Dynamic Data Validation, Indirect, Offset# anyone can help?

[Kevin_Qiu]

I used INDIRECT to get a list for Data Validation, but the result is totally different in these 2 scenarios.

Scenario 1: use offset to get a dinamic range, the result is error.

Scenario 2: get a range without OFFSET, the result is normal.

[#2]

@Kevin_Qiu 

offset formula needs a height and a width. In your formula you have no value for height which should have been COUNTA formula and 1 in your width area like below:

=OFFSET(Sheet1!$C$1;;;COUNTA(Sheet1!$C:$C);1)

if you do not enter any value in both height and width or enter 0 in these fields, offset formula would return #REF error.

[#3]

@erol sinan zorlu 

thank you so much for your time.

I revised the OFFSET formula and it referred to the right cells.

[#4]

thank you so much for your time.
I revised the OFFSET formula and it referred to the right cells.
after that, i use INDIRECT in the data validation, the result is still the same.

[#5]

@Kevin_Qiu 

https://chandoo.org/forum/threads/indirect-using-dynamic-named-range.12282/

[#6]

thank you so much. but it's really hard for me to understand even though I've spent a few hours on it.