Data Validation IF Statement (Too Many Characters)

[craig2525]

Hello all,
My if statement for a drop down list has too many characters. How would I reduce this to make it work? =IF(O3=$AA$11,$AA$13:$AA$27,IF(O3=$AB$11,$AB$13:$AB$27,IF(O3=$AC$11,$AC$13:$AC$27,IF(O3=$AD$11,$AD$13:$AD$27,IF(O3=$AE$11,$AE$13:$AE$27,IF(O3=$AF$11,$AF$13:$AF$27,IF(O3=$AG$11,$AG$13:$AG$27,IF(O3-7=$AI$11,$AI$13:$AI$27,IF(O3-7=$AJ$11,$AJ$13:$AJ$27,IF(O3-7=$AK$11,$AK$13:$AK$27,IF(O3-7=$AL$11,$AL$13:$AL$27,IF(O3-7=$AM$11,$AM$13:$AM$27,IF(O3-7=$AN$11,$AN$13:$AN$27,IF(O3-7=$AO$11,$AO$13:$AO$27,IF(O3+7=$AQ$11,$AQ$13:$AQ$27,IF(O3+7=$AR$11,$AR$13:$AR$27,IF(O3+7=$AS$11,$AS$13:$AS$27,IF(O3+7=$AT$11,$AT$13:$AT$27,IF(O3+7=$AU$11,$AU$13:$AU$27,IF(O3+7=$AV$11,$AV$13:$AV$27,IF(O3+7=$AW$11,$AW$13:$AW$27)))))))))))))))))))))
It is actually 3 different lists combined.

O3 is a date to set up which list to use. Previous week, current week and next week (each set of names is one of those weeks). Depending on the week, I need to use one of the lists.

 

[SanjayGMusafir]

With Excel 2016 in your hand, at best I can think of is using Named ranges for target cells. This can significantly shorten number of characters used in a formula.

Though not so sure it will work for you or not. You have to try and test it for yourselves.