Sep 18 2019 06:59 PM
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.
Sep 18 2019 11:52 PM
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.
Sep 19 2019 02:33 AM
thank you so much for your time.
I revised the OFFSET formula and it referred to the right cells.
Sep 19 2019 02:34 AM
Sep 20 2019 03:45 AM