Drop Down List

%3CLINGO-SUB%20id%3D%22lingo-sub-2912948%22%20slang%3D%22en-US%22%3EDrop%20Down%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2912948%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20wondering%20if%20someone%20could%20help.%20I%20want%20to%20add%20a%20formula%20to%20a%20drop-down%20list%20example%3A%26nbsp%3B%3C%2FP%3E%3CP%3EIFS(Interface_Funding_Type_%3D%22Refinanced%22%2CAE3%3AAE87%2CInterface_Funding_Type_%3D%22Solar%20Loan%22%2CAF3%3AAF10%2CInterface_Funding_Type_%3D%22Green%20Penny%22%2CAG3%3AAG4%2C'17.%20Interface'!I46%3D%22Cash%22%2C%220.00%25%22%3C%2FP%3E%3CP%3EIs%20it%20possible%20for%20the%20data%20validation%20drop%20down%20capable%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2912948%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2913026%22%20slang%3D%22en-US%22%3ERe%3A%20Drop%20Down%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2913026%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1017135%22%20target%3D%22_blank%22%3E%40ChrisIreland%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eput%20that%20formula%20in%20a%20sheet%20say%20you%20name%20a%20sheet%20%22Drop%20Downs%22%20and%20in%20A1%20you%20put%20that%20formula.%3C%2FP%3E%3CP%3Ein%20the%20data%20validation%20drop%20down%20you%20reference%20the%20cell%20A1%23%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2913049%22%20slang%3D%22en-US%22%3ERe%3A%20Drop%20Down%20List%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2913049%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1017135%22%20target%3D%22_blank%22%3E%40ChrisIreland%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%20it%20is%20possible.%20You%20just%20need%20to%20make%20some%20adjustments%20regarding%20Cash%20because%20as%20it%20is%20a%20static%20value%20it%20cannot%20be%20treated%20as%20a%20proper%20list.%3C%2FP%3E%3CP%3EFind%20attachment%20some%20idea%20so%20you%20can%20start%20with%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDIRECT(CHOOSE(MATCH(Interface_Funding_Type_%2CFunding_Type%3B0)%2C%22AE3%3AAE87%22%2C%22AF3%3AAF10%22%2C%22AG3%3AAG4%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JulianoPetrukio_0-1635845336320.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F323048iB5F94D4A5A0F26B5%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JulianoPetrukio_0-1635845336320.png%22%20alt%3D%22JulianoPetrukio_0-1635845336320.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

I was wondering if someone could help. I want to add a formula to a drop-down list example: 

IFS(Interface_Funding_Type_="Refinanced",AE3:AE87,Interface_Funding_Type_="Solar Loan",AF3:AF10,Interface_Funding_Type_="Green Penny",AG3:AG4,'17. Interface'!I46="Cash","0.00%"

Is it possible for the data validation drop down capable? 

2 Replies

@ChrisIreland 

 

put that formula in a sheet say you name a sheet "Drop Downs" and in A1 you put that formula.

in the data validation drop down you reference the cell =A1# 

@ChrisIreland 

Yes it is possible. You just need to make some adjustments regarding Cash because as it is a static value it cannot be treated as a proper list.

Find attachment some idea so you can start with

 

=INDIRECT(CHOOSE(MATCH(Interface_Funding_Type_,Funding_Type;0),"AE3:AE87","AF3:AF10","AG3:AG4"))

 

JulianoPetrukio_0-1635845336320.png

This solution can be applied for any of versions of excel.