Data Validation correct but dropdown list randomly not working

%3CLINGO-SUB%20id%3D%22lingo-sub-3179464%22%20slang%3D%22en-US%22%3EData%20Validation%20correct%20but%20dropdown%20list%20randomly%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3179464%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%20the%20title%20is%20pretty%20self-explanatory.%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EI%20have%20a%20tab%20with%20about%2030%20cells%20linked%20to%20a%20table%20in%20another%20tab%20with%20about%20200%20references.%26nbsp%3B%3C%2FLI%3E%3CLI%3EFor%20some%20reason%2C%20only%20the%20first%20half%20of%20cells%20work%20correctly%20and%20the%20rest%20don't%20show%20up%20the%20items%20I%20search.%3C%2FLI%3E%3CLI%3EThe%20data%20validation%20shows%20the%20correct%20items%20but%20the%20dropdown%20list%20does%20not.%3C%2FLI%3E%3CLI%3EI%20have%20the%20same%20setup%20in%20another%20workbook%20to%20generate%20invoices%20and%20it%20works%20like%20a%20charm.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EI%20could%20attach%20the%20workbook%20if%20someone%20asks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3179464%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

Hi all, the title is pretty self-explanatory. 

  • I have a tab with about 30 cells linked to a table in another tab with about 200 references. 
  • For some reason, only the first half of cells work correctly and the rest don't show up the items I search.
  • The data validation shows the correct items but the dropdown list does not.
  • I have the same setup in another workbook to generate invoices and it works like a charm.

 

 

Thank you in advance

2 Replies

@johnnycasali365 

I didn't catch the idea of such data structuring, but the logic of data validation is different for first cells and the rest. In first cells data validation list refers on spill which selects data based on the same cell. Rest of cells has one position shift for spill formula criteria.

You are absolutely right! Thank you very much Sergei