This is a real working formula in my google sheet. I have 5 cells in which the user chooses values from data validated drop down lists. This formula then chooses the sheet to filter then filters on those values. Obviously, my issue is speed. So I'm hoping if I create a custom function or functions I will improve performance of my calculations. My question is, what would be the best way to proceed. create one function or should I break it down into the various functions and build a formula from multiple custom functions. Will I see an improvement? I'm also considering not setting the formula until the user clicks an image button (to avoid the formula constantly recalculating while the user is making their picks.
=if(E4= "View All Contacts", filter('View All Contacts'!$A$1:$L$3999,if(I8<>"",'View All Contacts'!A:A =I8,'View All Contacts'!A:A <>""),if(E7<>"",'View All Contacts'!M:M =E7,'View All Contacts'!A:A <>""),if(E9<>"",'View All Contacts'!O:O =E9,if(E8 = "All Mile Stone Anniversaries",('View All Contacts'!N:N = (year(NOW()) - 1 )) + ('View All Contacts'!N:N = (year(Now()) - 5 ) )+ ('View All Contacts'!N:N = (year(Now()) - 10 ) )+ ('View All Contacts'!N:N = (year(Now()) - 15 ) )+ ('View All Contacts'!N:N = (year(Now()) - 20 )) + ('View All Contacts'!N:N = (year(Now()) - 25 ) )+ ('View All Contacts'!N:N = (year(Now()) - 30 ) )+ ('View All Contacts'!N:N = (year(Now()) - 40 ) )+ ('View All Contacts'!N:N = (year(Now()) - 50 )) + ('View All Contacts'!N:N = (year(Now()) - 60 ) )+ ('View All Contacts'!N:N = (year(Now()) - 70 )) + ('View All Contacts'!N:N = (year(Now()) - 75 ) )+ ('View All Contacts'!N:N = (year(Now()) - 80 ) )+ ('View All Contacts'!N:N = (year(Now()) - 90 )) + ('View All Contacts'!N:N = (year(Now()) - 100 )) + ('View All Contacts'!N:N = (year(Now()) - 125 ) )+ ('View All Contacts'!N:N = (year(Now()) - 150 ) )+ ('View All Contacts'!N:N = (year(Now()) - 175 )) + ('View All Contacts'!N:N = (year(Now()) - 200 )),If(E8 = ">100", ('View All Contacts'!N:N < (year(Now())) - 100) * ('View All Contacts'!N:N <>""), If(E8 >0, 'View All Contacts'!N:N = (year(NOW()) - E8 ),'View All Contacts'!A:A <>""))))),if(E4 = "View Active Contacts",filter('View Active Contacts'!$A$1:$L$3999,if(I8<>"",'View Active Contacts'!A:A =I8,'View Active Contacts'!A:A <>""),if(E7<>"",'View Active Contacts'!M:M =E7,'View Active Contacts'!A:A <>""),if(E9<>"",'View Active Contacts'!O:O =E9,if(E8 = "All Mile Stone Anniversaries",('View Active Contacts'!N:N = (year(NOW()) - 1 )) + ('View Active Contacts'!N:N = (year(Now()) - 5 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 10 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 15 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 20 )) + ('View Active Contacts'!N:N = (year(Now()) - 25 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 30 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 40 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 50 )) + ('View Active Contacts'!N:N = (year(Now()) - 60 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 70 )) + ('View Active Contacts'!N:N = (year(Now()) - 75 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 80 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 90 )) + ('View Active Contacts'!N:N = (year(Now()) - 100 )) + ('View Active Contacts'!N:N = (year(Now()) - 125 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 150 ) )+ ('View Active Contacts'!N:N = (year(Now()) - 175 )) + ('View Active Contacts'!N:N = (year(Now()) - 200 )),If(E8 = ">100", ('View Active Contacts'!N:N < (year(Now())) - 100) * ('View Active Contacts'!N:N <>""), If(E8 >0, 'View Active Contacts'!N:N = (year(NOW()) - E8 ),'View Active Contacts'!A:A <>""))))),if(E4 = "View Contacts from Companies Pending Current Payments",filter('View Contacts from Companies Pending Current Payments'!$A$1:$L$3999,if(I8<>"",'View Contacts from Companies Pending Current Payments'!A:A =I8,'View Contacts from Companies Pending Current Payments'!A:A <>""),if(E7<>"",'View Contacts from Companies Pending Current Payments'!M:M =E7,'View Contacts from Companies Pending Current Payments'!A:A <>""),if(E9<>"",'View Contacts from Companies Pending Current Payments'!O:O =E9,if(E8 = "All Mile Stone Anniversaries",('View Contacts from Companies Pending Current Payments'!N:N = (year(NOW()) - 1 )) + ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 5 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 10 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 15 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 20 )) + ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 25 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 30 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 40 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 50 )) + ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 60 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 70 )) + ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 75 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 80 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 90 )) + ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 100 )) + ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 125 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 150 ) )+ ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 175 )) + ('View Contacts from Companies Pending Current Payments'!N:N = (year(Now()) - 200 )),If(E8 = ">100", ('View Contacts from Companies Pending Current Payments'!N:N < (year(Now())) - 100) * ('View Contacts from Companies Pending Current Payments'!N:N <>""), If(E8 >0, 'View Contacts from Companies Pending Current Payments'!N:N = (year(NOW()) - E8 ),'View Contacts from Companies Pending Current Payments'!A:A <>""))))),if(E4 = "View Inactive Companies Contacts",filter('View Inactive Companies Contacts'!$A$1:$L$3999,if(I8<>"",'View Inactive Companies Contacts'!A:A =I8,'View Inactive Companies Contacts'!A:A <>""),if(E7<>"",'View Inactive Companies Contacts'!M:M =E7,'View Inactive Companies Contacts'!A:A <>""),if(E9<>"",'View Inactive Companies Contacts'!O:O =E9,if(E8 = "All Mile Stone Anniversaries",('View Inactive Companies Contacts'!N:N = (year(NOW()) - 1 )) + ('View Inactive Companies Contacts'!N:N = (year(Now()) - 5 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 10 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 15 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 20 )) + ('View Inactive Companies Contacts'!N:N = (year(Now()) - 25 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 30 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 40 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 50 )) + ('View Inactive Companies Contacts'!N:N = (year(Now()) - 60 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 70 )) + ('View Inactive Companies Contacts'!N:N = (year(Now()) - 75 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 80 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 90 )) + ('View Inactive Companies Contacts'!N:N = (year(Now()) - 100 )) + ('View Inactive Companies Contacts'!N:N = (year(Now()) - 125 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 150 ) )+ ('View Inactive Companies Contacts'!N:N = (year(Now()) - 175 )) + ('View Inactive Companies Contacts'!N:N = (year(Now()) - 200 )),If(E8 = ">100", ('View Inactive Companies Contacts'!N:N < (year(Now())) - 100) * ('View Inactive Companies Contacts'!N:N <>""), If(E8 >0, 'View Inactive Companies Contacts'!N:N = (year(NOW()) - E8 ),'View Inactive Companies Contacts'!A:A <>""))))),if(E4 = "View Contacts from Companies with Expired Memberships",filter('View Contacts from Companies with Expired Memberships'!$A$1:$L$3999,if(I8<>"",'View Contacts from Companies with Expired Memberships'!A:A =I8,'View Contacts from Companies with Expired Memberships'!A:A <>""),if(E7<>"",'View Contacts from Companies with Expired Memberships'!M:M =E7,'View Contacts from Companies with Expired Memberships'!A:A <>""),if(E9<>"",'View Contacts from Companies with Expired Memberships'!O:O =E9,if(E8 = "All Mile Stone Anniversaries",('View Contacts from Companies with Expired Memberships'!N:N = (year(NOW()) - 1 )) + ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 5 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 10 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 15 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 20 )) + ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 25 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 30 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 40 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 50 )) + ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 60 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 70 )) + ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 75 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 80 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 90 )) + ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 100 )) + ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 125 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 150 ) )+ ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 175 )) + ('View Contacts from Companies with Expired Memberships'!N:N = (year(Now()) - 200 )),If(E8 = ">100", ('View Contacts from Companies with Expired Memberships'!N:N < (year(Now())) - 100) * ('View Contacts from Companies with Expired Memberships'!N:N <>""), If(E8 >0, 'View Contacts from Companies with Expired Memberships'!N:N = (year(NOW()) - E8 ),'View Contacts from Companies with Expired Memberships'!A:A <>""))))),if(E4 = "View Contacts from Potential Companies",filter('View Contacts from Potential Companies'!$A$1:$L$3999,if(I8<>"",'View Contacts from Potential Companies'!A:A =I8,'View Contacts from Potential Companies'!A:A <>""),if(E7<>"",'View Contacts from Potential Companies'!M:M =E7,'View Contacts from Potential Companies'!A:A <>""),if(E9<>"",'View Contacts from Potential Companies'!O:O =E9,if(E8 = "All Mile Stone Anniversaries",('View Contacts from Potential Companies'!N:N = (year(NOW()) - 1 )) + ('View Contacts from Potential Companies'!N:N = (year(Now()) - 5 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 10 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 15 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 20 )) + ('View Contacts from Potential Companies'!N:N = (year(Now()) - 25 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 30 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 40 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 50 )) + ('View Contacts from Potential Companies'!N:N = (year(Now()) - 60 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 70 )) + ('View Contacts from Potential Companies'!N:N = (year(Now()) - 75 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 80 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 90 )) + ('View Contacts from Potential Companies'!N:N = (year(Now()) - 100 )) + ('View Contacts from Potential Companies'!N:N = (year(Now()) - 125 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 150 ) )+ ('View Contacts from Potential Companies'!N:N = (year(Now()) - 175 )) + ('View Contacts from Potential Companies'!N:N = (year(Now()) - 200 )),If(E8 = ">100", ('View Contacts from Potential Companies'!N:N < (year(Now())) - 100) * ('View Contacts from Potential Companies'!N:N <>""), If(E8 >0, 'View Contacts from Potential Companies'!N:N = (year(NOW()) - E8 ),'View Contacts from Potential Companies'!A:A <>""))))),""))))))
