How to use a drop down box to change a formula in a cell

Nigeljones

New Member
Joined
Apr 26, 2013
Messages
26
Office Version
  1. 365
Platform
  1. Windows
The best way to describe what I am after is with the excel sheet. The part of the sheet of most interest is rows 47 to 57. In section 5 I have 2 drop down lists at present the first one says 11/U Improvisations and the other says modern/lyrical. When you use these drop down lists the values change according to the submissions sheet. When the drop down list on the left goes to one of the values that says Duos or Trios I need all the formulas from 51 A to 56 L to change to the formulas from 51 M to 56 X. The reason for this is that Duos or Trios require 2 or 3 names whereas the others require only 1 name. I cannot seem to do a mini sheet and I also need 2 different sheets to perform the operations. Is there any way that I could upload the file.
 

Attachments

  • Excel.jpg
    Excel.jpg
    122.5 KB · Views: 40
The spreadsheet is actually a program for a dance eisteddfod. It works exactly as I want it to. I would use the drop down list where it says 11/u improvisation to select the age group and then the 2nd drop down list where it says modern/lyrical to select the type of dance. Then it would search the submissions sheet which I copy and paste from the entry forms and enter the performers names that correspond to the drop down lists. This will give me a list of names first name then surname. What I want is that when the first drop down list selects duos or trios the formulas that are in A51 to L56 need to change to the formulas that are in M51 to X56. That way the results would be one dancers name followed underneath with their duo partner then followed under that with their trio partner. My wife spends weeks trying to do the program, this would just make it so much easier.
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
This spreadsheet is a program for a dance eisteddfod. Selecting the first drop down list that says 11/u improvisation you can choose the age group, then selecting the 2nd drop down list that says modern/lyrical you can select the dance style. The sheet then searches the submissions sheet and inputs the results in the range of cells A51 to L56. What I am trying to do is when the 1st drop down list is used and selects duos or trios then the formulas A51 to L56 are replaced with the formulas M51 to X56. This is necessary as the results now would be the performers first name, then surname followed directly below this with their duo partners name then followed directly below this with their trio partners name.
 
Upvote 0
Nigel,

I wanted to replace all the SMALLs with AGGREGATE(15,6 s so I didn't have to Ctrl-Shift-Enter so much

I wanted to replace the hard coded =IF(AC51="","","1") with =((COLUMN()-COLUMN($AC$51)+1)/4)+(ROWS(AC51:$AC$51)*3)-2
and use them as the k value for the AGGREGATE

...but I've run out of time :(

My workaround (cheat?) would be to move the alternative calculations to columns on the right (which could be hidden) and then in your A51 to L56 cells just retrieve the appropriate result, like this:

NigelJones-V3.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAX
48Section2Start TimeSectionStart Time
49511/u ImprovisationsModern/Lyrical9:12 AM29/U Duos and TriosJazz12:18 AM
5033
51181LexieMustafa362SiennaSpence373SiennaSpence181LexieMustafa362SiennaSpence373SiennaSpence681ChelseaSantana        
52394MadilynLake odea415JessicaRobinson846NathanielDigges-Lagula394MadilynLake odea415JessicaRobinson846NathanielDigges-Lagula &00        
53                         &00        
54                                    
55                                    
56                                    
57Results: 1st………………..2nd………………..3rd………………..HC………………..Results: 1st………………..2nd………………..3rd………………..HC………………..
Program
Cell Formulas
RangeFormula
E48E48=LEN(AA51)
A49,M49A49=A36+1
L49,X49L49=L36+TIME(0,L50,0)+3
L50L50=((6-COUNTIF(C40:C45,""))*$M$4)+((6-COUNTIF(G40:G45,""))*$M$4)+(6-COUNTIF(K40:K45,""))*$M$4+$M$5+L47
X50X50=((6-COUNTIF(O38:O43,""))*$M$4)+((6-COUNTIF(S38:S43,""))*$M$4)+(6-COUNTIF(W38:W43,""))*$M$4+$M$5+X47
A51:L56A51=IF(ISNUMBER(SEARCH("Duos and Trios",$C$49)),INDEX($AM51:$AX51,1,COLUMN()-COLUMN($A49)+1),INDEX($AA51:$AL51,1,COLUMN()-COLUMN($A49)+1))
AA51AA51=SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$1))
AB51,AN51AB51=IF(AC51="","","1")
AC51:AD56AC51=IFERROR(INDEX(Submissions!B:B,$AA51),"")
AE51AE51=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$2)),"")
AF51,AR51AF51=IF(AG51="","","2")
AG51:AH56AG51=IFERROR(INDEX(Submissions!B:B,$AE51),"")
AI51AI51=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$3)),"")
AJ51,AV51AJ51=IF(AK51="","","3")
AK51,AK54:AL56,AK53,AK52:AL52AK51=IFERROR(INDEX(Submissions!B:B,$AI51),"")
AL51,AL53AL51=IFERROR(INDEX(Submissions!C:C,$AI51),"")
AM51AM51=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$1)),"")
AO51,AW54,AS54,AO54,AW51,AS51AO51=IFERROR(INDEX(Submissions!$B:$B,AM51),"")
AP51,AX54,AT54,AP54,AX51,AT51AP51=IFERROR(INDEX(Submissions!$C:$C,AM51),"")
AQ51AQ51=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$2)),"")
AU51AU51=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$3)),"")
AA52AA52=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$4)),"")
AB52,AN54AB52=IF(AC52="","","4")
AE52AE52=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$5)),"")
AF52,AR54AF52=IF(AG52="","","5")
AI52AI52=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$6)),"")
AJ52,AV54AJ52=IF(AK52="","","6")
AM52AM52=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$4)),"")
AV55:AV56,AR55:AR56,AN55:AN56,AN52:AN53,AV52:AV53,AR52:AR53AN52=IF(AO52="","","&")
AO52,AW55,AS55,AO55,AW52,AS52AO52=IFERROR(INDEX(Submissions!$Q:$Q,AM51),"")
AP52,AX55,AT55,AP55,AX52,AT52AP52=IFERROR(INDEX(Submissions!$R:$R,AM51),"")
AQ52AQ52=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$5)),"")
AU52AU52=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$6)),"")
AA53AA53=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$7)),"")
AB53AB53=IF(AC53="","","7")
AE53AE53=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$8)),"")
AF53AF53=IF(AG53="","","8")
AI53AI53=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$9)),"")
AJ53AJ53=IF(AK53="","","9")
AM53AM53=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$7)),"")
AO53,AW56,AS56,AO56,AW53,AS53AO53=IFERROR(INDEX(Submissions!$U:$U,AM51),"")
AP53,AX56,AT56,AP56,AX53,AT53AP53=IFERROR(INDEX(Submissions!$V:$V,AM51),"")
AQ53AQ53=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$8)),"")
AU53AU53=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$9)),"")
AA54AA54=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$10)),"")
AB54AB54=IF(AC54="","","10")
AE54AE54=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$11)),"")
AF54AF54=IF(AG54="","","11")
AI54AI54=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$12)),"")
AJ54AJ54=IF(AK54="","","12")
AM54AM54=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$10)),"")
AQ54AQ54=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$11)),"")
AU54AU54=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$12)),"")
AA55AA55=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$13)),"")
AB55AB55=IF(AC55="","","13")
AE55AE55=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$14)),"")
AF55AF55=IF(AG55="","","14")
AI55AI55=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$15)),"")
AJ55AJ55=IF(AK55="","","15")
AM55AM55=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$13)),"")
AQ55AQ55=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$14)),"")
AU55AU55=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$15)),"")
AA56AA56=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$16)),"")
AB56AB56=IF(AC56="","","16")
AE56AE56=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$17)),"")
AF56AF56=IF(AG56="","","17")
AI56AI56=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H49,OFFSET(Submissions!$A$2:$A$140,0,MATCH($C49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$140)),ROW($A$18)),"")
AJ56AJ56=IF(AK56="","","18")
AM56AM56=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$16)),"")
AQ56AQ56=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$17)),"")
AU56AU56=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T49,OFFSET(Submissions!$A$2:$A$140,0,MATCH(O49,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$140)),ROW(M$18)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Data Validation
CellAllowCriteria
C49:G49List=$N$4:$N$29
H49:K49List=$Q$4:$Q$25
O49:S49List=$N$4:$N$29
T49:W49List=$Q$4:$Q$25
 
Upvote 0
Solution
Great. This nearly works. Just some small changes and it may be right. I will play with it a while. Thanks for your help.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top