Forum Discussion
SP3990
Apr 12, 2025Copper Contributor
Display column as per list
Hi 1) i wish to display columns from H-L into column O based on my selection from the dropdown list on the right. how do i achieve that with minimum effort? 2) Another similar query...
- Apr 15, 2025
alternatively for 1)
=CHOOSECOLS(H3:L999, XMATCH(V7, H1:L1))as for 2) I would recommend you create some LAMBDA functions for each equation A-E. Here is an example for A:
FofT_A = LAMBDA(T, IFS( (T>0)*(T<=0.1), 0.8 + 15.5*T, T<=1.5, IF( 0.704/T<=2.35, 0.704/T, 2.35), T> 1.5, 1.056/T^2, TRUE, 0 ))Then I would use a CHOOSE and maybe a MAP over all the Time values:
=MAP(N3:N999, LAMBDA( T, CHOOSE( XMATCH(V8, H1:L1), FofT_A(T), FofT_B(T), FofT_C(T), FofT_D(T), FofT_E(T) ) ) )I recommend you have those A-E functions separate so you can easily use them anywhere on the sheet but if you need a single formula it might be something like (but you need to fix the values for each of the sub-LAMBDAs:
=LET(TimeVals, N3:N999, DropDown, V8, FofT_A = LAMBDA(T, IFS( (T>0)*(T<=0.1), 0.8 + 15.5*T, T<=1.5, IF( 0.704/T<=2.35, 0.704/T, 2.35), T> 1.5, 1.056/T^2, TRUE, 0 )), FofT_B = LAMBDA(T, IFS( (T>0)*(T<=0.1), 0.8 + 15.5*T, T<=1.5, IF( 0.704/T<=2.35, 0.704/T, 2.35), T> 1.5, 1.056/T^2, TRUE, 0 )), FofT_C = LAMBDA(T, IFS( (T>0)*(T<=0.1), 0.8 + 15.5*T, T<=1.5, IF( 0.704/T<=2.35, 0.704/T, 2.35), T> 1.5, 1.056/T^2, TRUE, 0 )), FofT_D = LAMBDA(T, IFS( (T>0)*(T<=0.1), 0.8 + 15.5*T, T<=1.5, IF( 0.704/T<=2.35, 0.704/T, 2.35), T> 1.5, 1.056/T^2, TRUE, 0 )), FofT_E = LAMBDA(T, IFS( (T>0)*(T<=0.1), 0.8 + 15.5*T, T<=1.5, IF( 0.704/T<=2.35, 0.704/T, 2.35), T> 1.5, 1.056/T^2, TRUE, 0 )), MAP(TimeVals, LAMBDA(T, CHOOSE( XMATCH(DropDown, H1:L1), FofT_A(T), FofT_B(T), FofT_C(T), FofT_D(T), FofT_E(T) ) ) ) )
SP3990
Apr 15, 2025Copper Contributor
Hi
For (1) i tried the formula but doesnt seem to work. can you pls check if i have input anything wrong
the value in cell O3 should display 0.136= value in cell H3 corresponding to my selection from dropdown cell V7=soil A but it is displaying 0.3995