Limit in excel data validation source code

[BrightJovanny]

I have an excel file with two sheets. The second sheet (Report) contains data validation cells based on the first sheet (Data). From the second sheet, the drop-down list that displays in the Select XXX depends on the selection in the Generate Report. When the Generate Report is set to anything beyond the first five in its list, the Select XXX displays year as a default list (no problem with this) via the code `...INDIRECT("Year")...`. The problem is that excel does not allow for addition of more code (seems I hit the limit). The question is - how can I manipulate this code to accommodate every option in the Generate Report? or perhaps, is there another method to implement?

The data validation source code for the drop-down list is

=IF($B$4=Data!$Q$5,INDIRECT("Client"), IF($B$4=Data!$Q$6,INDIRECT("Month"), IF($B$4=Data!$Q$7,INDIRECT("Product_Service"), IF($B$4=Data!$Q$8,INDIRECT("Sector"), IF($B$4=Data!$Q$9,INDIRECT("Trans_Type"),INDIRECT("Year"))))))

Please, see the sample file at <Sample.xlsx>

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this as your DV formula

=INDIRECT(CHOOSE(MATCH(B4,Data!$Q$5:$Q$12,0),"Client","Month","Product_Service","Sector","Trans_Type","Year","Year","Year"))

 

[Fluff]

Cross posted Limit in excel data validation source code

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[BrightJovanny]

Thank you very much. I never thought of using choose-match function, but rather than having to list all the range names, I think it would be useful to create a second column and refer to it. This has also been solved using Vlookup see here

Welcome to the MrExcel board!

Try this as your DV formula

=INDIRECT(CHOOSE(MATCH(B4,Data!$Q$5:$Q$12,0),"Client","Month","Product_Service","Sector","Trans_Type","Year","Year","Year"))