Formula not working in data validation

Question

Hi,

I'm using Excel 360 - web.

Goal: Trying to created a dynamic list based on the input of another cell.

I have created the following formula

=OFFSET('Index - What'!$B$2,1,MATCH($C2,'Index - What'!$B$2:$F$2,0)-1,COUNTA(OFFSET('Index - What'!$B$2,1,MATCH($C2,'Index - What'!$B$2:$F$2,0)-1,100)),1)

The above formula works when its inputted into a cell, but when I try and add the formula within the data validation (either under list or custom) I get the following error code, "This formula currently evaluates to an error. Do you want to continue?"

Anyone know why the formula works on its own? but not in the data validation tool?

Answer 1

Solved the issue, turns out I was using a free version of 365 and not the full version. The free version did not support functions.

Answer 2

Just to clarify, when you say add a name, how would that show?

Should formulas not work in the data validation? Is that a restriction?

Answer 3

You are welcome. Share some data and show the expected result.

Answer 4

Thanks, just tried that, it partially works, but it now only pulls the first lane of the offset and not the full list. :(

Answer 5

Hi,

Assign a name to the formula and use that name in Data validation.