Custom formula in Data Validation not allowing me to "Save"-CodePudding

=IF(VLOOKUP(B10,({'Roster Data'!B:B,'Roster Data'!A:A}),2,FALSE="WR",'Roster Data'!H2:H11,'Roster Data'!G2:G11)

When I put this formula into the CUSTOM FORMULA option for DATA VALIDATION, I hit the SAVE button but it doesn't save. Is there something wrong with my formula or am I just writing the formula wrong for the DATA VALIDATION process.

Basically, if a PLAYERS POSITION is RB, I want the DATA VALIDATION to go to the RB list and if the PLAYERS POSITION is a WR, I want the DATA VALIDATION to go to the WR list.

CodePudding user response：

I think your formula is wrong.

Use this one and see if it works for you

=IF(VLOOKUP(B10,{'Roster Data'!B:B,'Roster Data'!A:A},2,0)="WR",'Roster Data'!H2:H11,'Roster Data'!G2:G11)

CodePudding user response：

You can't include references to other pages in formula fields in google sheets. You can either to the computation and select the range on another page as a list or have helper columns on the same page.

CodePudding user response：

for Data Validation applies the same rules as for Conditional Formatting... referencing range from different sheet needs to be indirected. therefore the correct formula should be:

=IF(IFNA(VLOOKUP(B10, {INDIRECT("Roster Data!B:B"), INDIRECT("Roster Data!A:A")}, 2, 0))="WR", 
 INDIRECT("Roster Data!H2:H11"), INDIRECT("Roster Data!G2:G11"))

and such formula can be used here for validation purposes:

if you want to use it here for dropdown purposes:

that is not possible and you will need to do it like this: