Data Validation based on 2criteria

[Beatrix]

Hi ,

I need to update the formula in data validation list in column G in data validation tab. Dropdown list in column G was based on only Area Type before but now it should work based on Client Type and Are Type. Therefore I added more lists into IP_Codes

Example: If Client Type is Tea, Area Type is EY then IP dropdown list should bring the list called EY_Tea.

Current formula is below:

=OFFSET(IP_Codes,1,MATCH(F11,Areas,0)-1,COUNTA(INDEX(IP_Data,0,MATCH(F11,Areas,0)))-1,1)

I thought I could update Areas named range as same as IP codes to get this formula to work but I need to keep Area Type list as it is.

Does anyone know how to update above formula to make IP data validation list based on 2 criteria?

I attached the spreadsheet.
Your help would be much appreciated.

Cheers
Yeliz

[Bob Phillips]

Try

=IF($F$10="EY",EY_Tea,OFFSET(IP_Codes,1,MATCH(F10,Areas,0)-1,COUNTA(INDEX(IP_Data,0,MATCH(F10,Areas,0)))-1,1))

[Beatrix]

Thanks very much for your reply..

Actually I can't apply IF statement for 14 categories as there is a character restriction in data validation formula as the formula gets too long this way. Is there any other way to do this?

Cheers,
Yeliz

[Bob Phillips]

Do an INDIRECT on a VLOOKUP result.