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: 9

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,391
Office Version
  1. 2016
Platform
  1. Windows
Hi NigelJones,

It's difficult to recommend a particular solution without seeing the formulae. If it's a common formula but with one different value then just a MATCH would work but if the formulae are different then something like CHOOSE may be best.

Here's an example of the CHOOSE solution.

NigelJones.xlsx
CDEFGHIJKLM
48NameValue
4911/u ImprovisationModern/Lyrical11/u Improvisation1
50Modern/Lyrical2
515.3333333335.3333335.333333KatKatKatJazz/Funk3
52HipHop/Clasical4
53
Sheet1
Cell Formulas
RangeFormula
C51:E51C51=CHOOSE(INDEX($M$49:$M$60,MATCH($C$49,$L$49:$L$60,0)),16/3,"Kat",55+ROW(),"Lizt")
H51:J51H51=CHOOSE(INDEX($M$49:$M$60,MATCH($H$49,$L$49:$L$60,0)),16/3,"Kat",55+ROW(),"Lizt")
Cells with Data Validation
CellAllowCriteria
C49List=OFFSET($L$49,,,COUNTA($L$49:$L$60))
H49List=OFFSET($L$49,,,COUNTA($L$49:$L$60))
 

Nigeljones

New Member
Joined
Apr 26, 2013
Messages
26
Office Version
  1. 365
Platform
  1. Windows
The formulas i have use the drop down list 11/u improvisation AND modern/lyrical to give the results from the submissions sheet. If from the first drop down list 9/u duos is chosen then all the formulas from 51a to 56x would be replaced by a different formula as the results have to come from a different part of the submissions sheet. Is this possible?
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,391
Office Version
  1. 2016
Platform
  1. Windows
You'll have to give more information. Can I see the formulae and how they change?

XL2BB would be best but if you can select Formulas, Show Formulas and post images with 2 or 3 different dropdown selections then that would help.
 

Nigeljones

New Member
Joined
Apr 26, 2013
Messages
26
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

EBSDN Program with Formulas2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1
2SectionStart Time
3111/u ImprovisationsContemporary9:00:00 AMTimeAge GroupsRoutines
43Babies 5/uClassical Ballet
5361SiennaSpence372SiennaSpence393MadilynLake odea3SOLO Open 7/uDemi Character
6414JessicaRobinson845NathanielDigges-Lagula##6AngelinaPatoloSOLO Restricted 7/uStandard Jazz
7##7AngelinaPatolo##8BritneyKnight##9IsabelleCampbell7/u ImprovisationsHip Hop
8##10KobyOBrien##11JadeIrving    SOLO Open 9/uBroadway Jazz
9            SOLO Restricted 9/uContemporary
10            9/u ImprovisationsModern/Lyrical
11Results: 1st………………..2nd………………..3rd………………..HC………………..SOLO Open 11/uFast Tap
12SOLO Restricted 11/uSlow/Straight Tap
13SectionStart Time11/u ImprovisationsWaltz Tap
14211/u ImprovisationsModern/Lyrical9:36 amSOLO Open 13/uSong and Dance
1536SOLO Restricted 13/uStudent Choreography
16181LexieMustafa362SiennaSpence373SiennaSpence13/u ImprovisationsDance Own Choice
17394MadilynLake odea415JessicaRobinson846NathanielDigges-LagulaSOLO Open 15/uSinging Ballad
18##7AngelinaPatolo##8AngelinaPatolo##9BritneyKnightSOLO Restricted 15/uSinging Uptempo
19##10IsabelleCampbell##11KobyOBrien##12JadeIrving15/u ImprovisationsSinging Musical Theater
20##13SiennaAllitt        SOLO Open AgeSinging Disney or Pixar
21            SOLO Restricted O/ASinging Country
22Results: 1st………………..2nd………………..3rd………………..HC………………..Open Age ImprovisationsSinging Own Choice
237/U Duos and TriosJazz
24SectionStart Time9/U Duos and TriosTap
25311/u ImprovisationsJazz10:18 am11/U Duos and Trios
264213/U Duos and Trios
27181LexieMustafa362SiennaSpence373SiennaSpence15/U Duos and Trios
28394MadilynLake odea415JessicaRobinson846NathanielDigges-LagulaOpen Age Duos and Trios
29##7AngelinaPatolo##8AngelinaPatolo##9LahniHepi
30##10BritneyKnight##11IsabelleCampbell##12KobyOBrien
31##13JadeIrving##14SiennaAllitt    
32            
33Results: 1st………………..2nd………………..3rd………………..HC………………..
34
35SectionStart TimeSectionStart Time
36411/u ImprovisationsModern/Lyrical11:03 am19/U Duos and TriosJazz12:03 am
37453
38181LexieMustafa362SiennaSpence373SiennaSpence681ChelseaSantana##2BronteIreland##3TeganSims
39394MadilynLake odea415JessicaRobinson846NathanielDigges-Lagula##&EmmyWalker &MayaDalgleish &JaidaGagnuss
40##7AngelinaPatolo##8AngelinaPatolo##9BritneyKnight &SiennaCarson &DrewLuland &00
41##10IsabelleCampbell##11KobyOBrien##12JadeIrving            
42##13SiennaAllitt                    
43                        
44Results: 1st………………..2nd………………..3rd………………..HC………………..Results: 1st………………..2nd………………..3rd………………..HC………………..
45
46
Program
Cell Formulas
RangeFormula
A5,A38,A27,A16A5=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$1)),"")
B5,B38,N38,B27,B16B5=IF(C5="","","1")
C5:D10,C38:D43,C27:D32,C16:D21C5=IFERROR(INDEX(Submissions!B:B,$A5),"")
E5,E38,E27,E16E5=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$2)),"")
F5,F38,R38,F27,F16F5=IF(G5="","","2")
G5:H10,G38:H43,G27:H32,G16:H21G5=IFERROR(INDEX(Submissions!B:B,$E5),"")
I5,I38,I27,I16I5=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$3)),"")
J5,J38,V38,J27,J16J5=IF(K5="","","3")
K5:K10,K38:L43,K27:L32,K16:L21K5=IFERROR(INDEX(Submissions!B:B,$I5),"")
L5:L10L5=IFERROR(INDEX(Submissions!C:C,$I5),"")
A6,A39,A28,A17A6=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$4)),"")
B6,B39,N41,B28,B17B6=IF(C6="","","4")
E6,E39,E28,E17E6=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$5)),"")
F6,F39,R41,F28,F17F6=IF(G6="","","5")
I6,I39,I28,I17I6=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$6)),"")
J6,J39,V41,J28,J17J6=IF(K6="","","6")
A7,A40,A29,A18A7=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$7)),"")
B7,B40,B29,B18B7=IF(C7="","","7")
E7,E40,E29,E18E7=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$8)),"")
F7,F40,F29,F18F7=IF(G7="","","8")
I7,I40,I29,I18I7=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$9)),"")
J7,J40,J29,J18J7=IF(K7="","","9")
A8A8=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$10)),"")
B8,B41,B30,B19B8=IF(C8="","","10")
E8,E41,E30,E19E8=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$11)),"")
F8,F41,F30,F19F8=IF(G8="","","11")
I8,I41,I30,I19I8=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$12)),"")
J8,J41,J30,J19J8=IF(K8="","","12")
A9,A42,A31,A20A9=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$13)),"")
B9,B42,B31,B20B9=IF(C9="","","13")
E9,E42,E31,E20E9=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$14)),"")
F9,F42,F31,F20F9=IF(G9="","","14")
I9,I42,I31,I20I9=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$15)),"")
J9,J42,J31,J20J9=IF(K9="","","15")
A10,A43,A32,A21A10=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$16)),"")
B10,B43,B32,B21B10=IF(C10="","","16")
E10,E43,E32,E21E10=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$17)),"")
F10,F43,F32,F21F10=IF(G10="","","17")
I10I10=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$18)),"")
J10,J43,J32,J21J10=IF(K10="","","18")
A14,A36,A25A14=A3+1
L14,L36,L25L14=L3+TIME(0,L15,0)+3
L15,L37,L26L15=((6-COUNTIF(C5:C10,""))*$M$4)+((6-COUNTIF(G5:G10,""))*$M$4)+(6-COUNTIF(K5:K10,""))*$M$4+$M$5+L12
A19,A41,A30A19=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H14,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C14,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$10)),"")
I21,I43,I32I21=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H14,OFFSET(Submissions!$A$2:$A$140,0,MATCH($C14,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$140)),ROW($A$18)),"")
M36M36=M23+1
X36X36=X23+TIME(0,X37,0)+3
X37X37=((6-COUNTIF(O25:O30,""))*$M$4)+((6-COUNTIF(S25:S30,""))*$M$4)+(6-COUNTIF(W25:W30,""))*$M$4+$M$5+X34
M38M38=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$1)),"")
O38,S38,W38,O41,S41,W41O38=IFERROR(INDEX(Submissions!$B:$B,M38),"")
P38,T38,X38,P41,T41,X41P38=IFERROR(INDEX(Submissions!$C:$C,M38),"")
Q38Q38=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$2)),"")
U38U38=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$3)),"")
M39M39=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$4)),"")
N39:N40,R39:R40,V39:V40,N42:N43,R42:R43,V42:V43N39=IF(O39="","","&")
O39,S39,W39,O42,S42,W42O39=IFERROR(INDEX(Submissions!$Q:$Q,M38),"")
P39,T39,X39,P42,T42,X42P39=IFERROR(INDEX(Submissions!$R:$R,M38),"")
Q39Q39=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$5)),"")
U39U39=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$6)),"")
M40M40=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$7)),"")
O40,S40,W40,O43,S43,W43O40=IFERROR(INDEX(Submissions!$U:$U,M38),"")
P40,T40,X40,P43,T43,X43P40=IFERROR(INDEX(Submissions!$V:$V,M38),"")
Q40Q40=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$8)),"")
U40U40=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$9)),"")
M41M41=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$10)),"")
Q41Q41=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$11)),"")
U41U41=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$12)),"")
M42M42=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$13)),"")
Q42Q42=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$14)),"")
U42U42=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$15)),"")
M43M43=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$16)),"")
Q43Q43=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$17)),"")
U43U43=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$140,0,MATCH(O36,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
C25:G25List=$N$4:$N$29
H25:K25List=$Q$4:$Q$25
C14:G14List=$N$4:$N$29
H14:K14List=$Q$4:$Q$25
C36:G36List=$N$4:$N$29
H36:K36List=$Q$4:$Q$25
C3:G3List=$N$4:$N$29
H3:K3List=$Q$4:$Q$25
O36:S36List=$N$4:$N$29
T36:W36List=$Q$4:$Q$25
 

Nigeljones

New Member
Joined
Apr 26, 2013
Messages
26
Office Version
  1. 365
Platform
  1. Windows
EBSDN Program with Formulas2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1Submission DateFirst NameLast NameDuo Partner Name (First Name)Duo Partner Name (Last Name)Trio Partner Name (First Name)Trio Partner Name (Last Name)Babies 5/uAmount $SOLO Restricted 7/uSOLO Open 7/u7/U Duos and Trios7/U ImprovisationsSOLO Restricted 9/uSOLO Open 9/u9/U Duos and Trios9/U ImprovisationsSOLO Restricted 11/uSOLO Open 11/u11/U Duos and Trios11/U ImprovisationsSOLO Restricted 13/uSOLO Open 13/u13/U Duos and Trios13/U ImprovisationsSOLO Restricted 15/uSOLO Open 15/u15/U Duos and Trios15/U ImprovisationsSOLO Restricted Open AgeSOLO Open AgeOpen Age Duos and Trios
2########TAHLIBORGE0Standard Jazz $25.00 Contemporary $25.00 Modern/Lyrical $25.00
3########SavannahDigges-Lagula0Hip Hop $25.00 Contemporary $25.00 Modern/Lyrical $25.00Tap $20.00
4########IslaHarkin0Standard Jazz $25.00 Fast Tap $25.00Broadway Jazz $25.00 Modern/Lyrical $25.00
5########RosemaryFinnerty0Singing Own Choice $25.00 Singing Ballad $25.00 Singing Musical Theater $25.00 Singing Disney or Pixar $25.00
6########LucaButler0Contemporary $25.00Classical Ballet $25.00 Standard Jazz $25.00 Broadway Jazz $25.00 Modern/Lyrical $25.00
7########KiaraChilby0Modern/Lyrical $25.00 Dance Own Choice $25.00Hip Hop $25.00 Standard Jazz $25.00 Broadway Jazz $25.00 Contemporary $25.00 Fast Tap $25.00 Slow/Straight Tap $25.00 Mr or Miss EBSDN $35.00
8########KeiraBonneyEmilyCadden0Jazz $35.00 Modern/Lyrical $35.00
9########JazmineGagnuss0Modern/Lyrical $25.00
10########EmilyVaughan0Classical Ballet $25.00 Demi Character $25.00 Broadway Jazz $25.00 Contemporary $25.00 Modern/Lyrical $25.00 Student Choreography $25.00 Dance Own Choice $25.00 EBSDN (Shining Stars) $25.00
11########EmilyVaughan0Classical Ballet $25.00 Demi Character $25.00 Broadway Jazz $25.00 Contemporary $25.00 Modern/Lyrical $25.00 Student Choreography $25.00 Dance Own Choice $25.00 EBSDN (Shining Stars) $25.00
12########LuaRiddle0Contemporary $25.00 Modern/Lyrical $25.00Standard Jazz $25.00
13########AllegraBenedettiDemi Character $25.00 Classical Ballet $25.00 Tap Any Style $25.00 Modern/Lyrical $25.00100
14########EricaShalders0Standard Jazz $25.00 Modern/Lyrical $25.00
15########ScarlettHaymanJazz $25.00 Modern/Lyrical $25.0050
16########HayleyPierce0Standard Jazz $25.00 Fast Tap $25.00
17########DimiKolovos0Singing Own Choice $25.00 Singing Musical Theatre $25.00Singing Ballad $25.00 Singing Up Tempo $25.00 Singing Country $25.00 Championships $35.00 Mr or Miss EBSDN $35.00
18########LexieMustafa0Standard Jazz $25.00 Modern/Lyrical $25.00Jazz $20.00 Modern/Lyrical $20.00
19########PhoebeMustafaLexieMustafa0Modern/Lyrical $25.00Modern/Lyrical $35.00Jazz $20.00 Modern/Lyrical $20.00
20########Isabella-RoseHuele0Classical Ballet $25.00 Modern/Lyrical $25.00
21########TahliaRobson0Contemporary $25.00 Modern/Lyrical $25.00
22########ShiaraRobson0Hip Hop $25.00 Standard Jazz $25.00 Modern/Lyrical $25.00
23########MontanaBressan0Classical Ballet $25.00 Modern/Lyrical $25.00
24########AddisonZieba0Classical Ballet $25.00 Demi Character $25.00 Standard Jazz $25.00 Modern/Lyrical $25.00
25########GeorgiaColeman0Fast Tap $25.00 Slow/Straight Tap $25.00 Song and Dance $25.00 Singing Own Choice $25.00
26########GeorgiaColeman0Fast Tap $25.00 Slow/Straight Tap $25.00 Song and Dance $25.00 Singing Own Choice $25.00
27########PaytenSims0Standard Jazz $25.00
28########HayleyUnderwood0Standard Jazz $25.00 Broadway Jazz $25.00 Contemporary $25.00 Modern/Lyrical $25.00 Student Choreography $25.00 Dance Own Choice $25.00
29########MichellerAelxander0Singing own Choice $25.00 Singing Ballad $25.00 Singing Up Tempo $25.00 Singing Musical Theatre $25.00 Singing Disney or Pixar $25.00 Singing Country $25.00
30########ElyceAlexander0Singing Own Choice $25.00 Singing Ballad $25.00 Singing Up-tempo $25.00 Singing Musical Theatre $25.00 Singing Country $25.00Singing Disney or Pixar $25.00
31########LucindaMartiniello0Standard Jazz $25.00 Broadway Jazz $25.00Jazz $20.00
32########RachelHamilton0Slow/Straight Tap $25.00
33########AdamBowser0Hip Hop $25.00
34########HannahHarvet0Standard Jazz $25.00 Modern/Lyrical $25.00 Dance Own Choice $25.00Mr or Miss EBSDN $35.00
35########AmeliaCaruana0Slow/Straight Tap $25.00
36########SiennaSpenceMadilynLakeodea0Contemporary $25.00Standard Jazz $25.00 Broadway Jazz $25.00 Song and Dance $25.00 Student Choreography $25.00 Dance Own Choice $25.00 Mr or Miss EBSDN $35.00Jazz $20.00 Modern/Lyrical $20.00 Contemporary $20.00
37########SiennaSpenceMadilynLake Odea0Contemporary $25.00Standard Jazz $25.00 Broadway Jazz $25.00 Song and Dance $25.00 Student Choreography $25.00 Dance Own Choice $25.00 Mr or Miss EBSDN $35.00Jazz $20.00 Modern/Lyrical $20.00 Contemporary $20.00
38########ZaniahHourigan Carrera0Contemporary $25.00Classical Ballet $25.00 Demi Character $25.00 Standard Jazz $25.00 Broadway Jazz $25.00 Modern/Lyrical $25.00
39########MadilynLake odeaSiennaSpence0Standard Jazz $25.00 Contemporary $25.00Hip Hop $25.00 Broadway Jazz $25.00 Modern/Lyrical $25.00 Student Choreography $25.00 Dance Own Choice $25.00 Mr or Miss EBSDN $35.00Contemporary $35.00Jazz $20.00 Modern/Lyrical $20.00 Contemporary $20.00
40########RubyWhitney0Dance Own Choice $25.00
41########JessicaRobinson0Hip Hop $25.00 Contemporary $25.00 Dance Own Choice $25.00Modern/Lyrical $25.00 Student Choreography $25.00 EBSDN ( Shining Stars) $25.00Jazz $20.00 Modern/Lyrical $20.00 Contemporary $20.00
42########RosePower0Standard Jazz $25.00Classical Ballet $25.00 Demi Character $25.00 Contemporary $25.00 Modern/Lyrical $25.00 Student Choreography $25.00Jazz $20.00 Modern/Lyrical $20.00 Contemporary $20.00
43########PaytenSims0Standard Jazz $25.00
44########KahlanCartwright0Modern/Lyrical $25.00
45########EllaCoccia0Singing Own Choice $25.00 Singing Ballad $25.00 Singing Up Tempo $25.00 Singing Musical Theatre $25.00 Championships $35.00
46########ElissaVasquez0Singing Own Choice $25.00 Singing Ballad $25.00
47########SadeVasquez0Singing own Choice $25.00 Singing Ballad $25.00 Singing Disney or Pixar $25.00
48########ChloeTerzisJazz $25.00 Modern/Lyrical $25.00 Contemporary $25.00 Dance Own Choice $25.00 EBSDN ( Shining Stars) $25.00125
49########RubyMuscat0Standard Jazz $25.00 EBSDN ( Shining Stars) $25.00Jazz $20.00 Contemporary $20.00
50########RubyMuscat0Standard Jazz $25.00 EBSDN ( Shining Stars) $25.00Jazz $20.00 Contemporary $20.00
51########BrooklynCappleJazz $25.00 Modern/Lyrical $25.00 Contemporary $25.00 Dance Own Choice $25.00 Championships $35.00 EBSDN ( Shining Stars) $25.00160
52########LorelaiHodges0Singing Own Choice $25.00 Singing Ballad $25.00 Singing Up-tempo $25.00 Singing Musical Theatre $25.00 Singing Country $25.00Singing Disney or Pixar $25.00
53########RubyWhitney0Dance Own Choice $25.00
54########RebekeahDunn0Contemporary $25.00 Modern/Lyrical $25.00Student Choreography $25.00Modern/Lyrical $20.00 Contemporary $20.00
55########BrooklynCappleJazz $25.00 Modern/Lyrical $25.00 Contemporary $25.00 Dance Own Choice $25.00 Championships $35.00 EBSDN ( Shining Stars) $25.00160
56########AshleighBlinco0Dance Own Choice $25.00
57########MiaGibbons0Singing Ballad $25.00 Singing Musical Theatre $25.00Singing own Choice $25.00
58########KeziahWhitford0Contemporary $25.00 Modern/Lyrical $25.00
59########MichelleCox0
60########KaylaCox0
61########HollyNutland0Classical Ballet $25.00 Standard Jazz $25.00 Hip Hop $25.00 Modern/Lyrical $25.00Song and Dance $25.00 Student Choreography $25.00Jazz $20.00 Tap $20.00 Modern/Lyrical $20.00
62########KaylaCox0
63########SiennaGreen0Hip Hop $25.00 Standard Jazz $25.00 Contemporary $25.00 Modern/Lyrical $25.00 Student Choreography $25.00 Championships $35.00
64########MacushlaShields0Classical Ballet $25.00 Contemporary $25.00 Modern/Lyrical $25.00
65########TylarSmith0Singing Own Choice $25.00 Championships $35.00Singing Ballad $25.00 Singing Up Tempo $25.00 Singing Musical Theatre $25.00 Singing Country $25.00 Mr or Miss EBSDN $35.00
66########JoceyleneMerritt0Classical Ballet $25.00 Demi Character $25.00 Standard Jazz $25.00 Contemporary $25.00 Modern/Lyrical $25.00 Dance Own Choice $25.00
67########OliviaNaumoski0Singing Ballad $25.00 Singing Uptempo $25.00
68########ChelseaSantanaEmmyWalkerSiennaCarson0Standard Jazz $25.00 Hip Hop $25.00 Contemporary $25.00 Modern/Lyrical $25.00 Student Choreography $25.00 Championships $35.00 Mr or Miss EBSDN $35.00 EBSDN ( Shining Stars) $25.00Jazz $35.00 EBSDN ( Shining Stars) $35.00
69########MackaylaClark0Contemporary $25.00Modern/Lyrical $25.00
70########MiaVan Haarlem0Standard Jazz $25.00 Contemporary $25.00 Modern/Lyrical $25.00 Championships $35.00 Mr or Miss EBSDN $35.00Modern/Lyrical $20.00
71########GeorgiaMurtas0Classical Ballet $25.00 Modern/Lyrical $25.00
72########CharlotteMurtasModern/Lyrical $25.0025
73########GeorgiaMurtas0Classical Ballet $25.00 Modern/Lyrical $25.00
74########CharlotteMurtasModern/Lyrical $25.0025
75########CharlotteMurtasModern/Lyrical $25.0025
76########GeorgiaMurtas0Classical Ballet $25.00 Modern/Lyrical $25.00
77########AnnalyseSullivanJazz $25.0025
78########CelesteAndimisarisClassical Ballet $25.0025
79########CelesteAndimisarisClassical Ballet $25.0025
80########AshleighCharmaine Blinco0Classical Ballet $25.00 Demi Character $25.00 Standard Jazz $25.00 Hip Hop $25.00 Contemporary $25.00 Modern/Lyrical $25.00Contemporary $20.00
81########ShakiraCuzner0Modern/Lyrical $25.00
82########JasmineArgyropoulos0Singing own Choice $25.00 Singing Ballad $25.00 Singing Musical Theatre $25.00
83########AmberSwadling0Contemporary $25.00 Modern/Lyrical $25.00
84########NathanielDigges-LagulaAbbeyIrmisch0Hip Hop $25.00 Contemporary $25.00 EBSDN (Shining Stars) $25.00Modern/Lyrical $25.00 Student Choreography $25.00Contemporary $35.00Jazz $20.00 Tap $20.00 Modern/Lyrical $20.00 Contemporary $20.00
85########SiennaCaruana0Broadway Jazz $25.00
86########LibbyMarnell0Contemporary $25.00Song and Dance $25.00
87########AmeliaWhittaker0Classical Ballet $25.00 Hip Hop $25.00 Broadway Jazz $25.00 Modern/Lyrical $25.00 Student Choreography $25.00 Championships $35.00 Mr or Miss EBSDN $35.00
88########AlexiaCifuentes0Singing Own Choice $25.00 Singing Ballad $25.00 Singing Musical Theater $25.00
89########CharlotteDekker0Standard Jazz $25.00
90########SophieTowill0Singing Own Choice $25.00 Singing Musical Theater $25.00
Submissions
 

Nigeljones

New Member
Joined
Apr 26, 2013
Messages
26
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

the 2nd mini sheet is the submissions sheet. I will have to alter the 1st sheet and i will resend it.
 

Nigeljones

New Member
Joined
Apr 26, 2013
Messages
26
Office Version
  1. 365
Platform
  1. Windows
EBSDN Program with Formulas2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1
2SectionStart Time
3111/u ImprovisationsContemporary9:00:00 AMTimeAge GroupsRoutines
43Babies 5/uClassical Ballet
5361SiennaSpence372SiennaSpence393MadilynLake odea3SOLO Open 7/uDemi Character
6414JessicaRobinson845NathanielDigges-Lagula    SOLO Restricted 7/uStandard Jazz
7            7/u ImprovisationsHip Hop
8            SOLO Open 9/uBroadway Jazz
9            SOLO Restricted 9/uContemporary
10            9/u ImprovisationsModern/Lyrical
11Results: 1st………………..2nd………………..3rd………………..HC………………..SOLO Open 11/uFast Tap
12SOLO Restricted 11/uSlow/Straight Tap
13SectionStart Time11/u ImprovisationsWaltz Tap
14211/u ImprovisationsModern/Lyrical9:18 amSOLO Open 13/uSong and Dance
1518SOLO Restricted 13/uStudent Choreography
16181LexieMustafa362SiennaSpence373SiennaSpence13/u ImprovisationsDance Own Choice
17394MadilynLake odea415JessicaRobinson846NathanielDigges-LagulaSOLO Open 15/uSinging Ballad
18            SOLO Restricted 15/uSinging Uptempo
19            15/u ImprovisationsSinging Musical Theater
20            SOLO Open AgeSinging Disney or Pixar
21            SOLO Restricted O/ASinging Country
22Results: 1st………………..2nd………………..3rd………………..HC………………..Open Age ImprovisationsSinging Own Choice
237/U Duos and TriosJazz
24SectionStart Time9/U Duos and TriosTap
25311/u ImprovisationsJazz9:39 am11/U Duos and Trios
262113/U Duos and Trios
27181LexieMustafa362SiennaSpence373SiennaSpence15/U Duos and Trios
28394MadilynLake odea415JessicaRobinson846NathanielDigges-LagulaOpen Age Duos and Trios
29            
30            
31            
32            
33Results: 1st………………..2nd………………..3rd………………..HC………………..
34
35SectionStart TimeSectionStart Time
36411/u ImprovisationsModern/Lyrical10:00 am19/U Duos and TriosJazz12:03 am
37213
38181LexieMustafa362SiennaSpence373SiennaSpence681ChelseaSantana        
39394MadilynLake odea415JessicaRobinson846NathanielDigges-Lagula &EmmyWalker        
40             &SiennaCarson        
41                        
42                        
43                        
44Results: 1st………………..2nd………………..3rd………………..HC………………..Results: 1st………………..2nd………………..3rd………………..HC………………..
45
46
Program
Cell Formulas
RangeFormula
A5,A38,A27,A16A5=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$1)),"")
B5,B38,N38,B27,B16B5=IF(C5="","","1")
C5:D10,C38:D43,C27:D32,C16:D21C5=IFERROR(INDEX(Submissions!B:B,$A5),"")
E5,E38,E27,E16E5=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$2)),"")
F5,F38,R38,F27,F16F5=IF(G5="","","2")
G5:H10,G38:H43,G27:H32,G16:H21G5=IFERROR(INDEX(Submissions!B:B,$E5),"")
I5,I38,I27,I16I5=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$3)),"")
J5,J38,V38,J27,J16J5=IF(K5="","","3")
K5:K10,K38:L43,K27:L32,K16:L21K5=IFERROR(INDEX(Submissions!B:B,$I5),"")
L5:L10L5=IFERROR(INDEX(Submissions!C:C,$I5),"")
A6,A39,A28,A17A6=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$4)),"")
B6,B39,N41,B28,B17B6=IF(C6="","","4")
E6,E39,E28,E17E6=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$5)),"")
F6,F39,R41,F28,F17F6=IF(G6="","","5")
I6,I39,I28,I17I6=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$6)),"")
J6,J39,V41,J28,J17J6=IF(K6="","","6")
A7,A40,A29,A18A7=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$7)),"")
B7,B40,B29,B18B7=IF(C7="","","7")
E7,E40,E29,E18E7=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$8)),"")
F7,F40,F29,F18F7=IF(G7="","","8")
I7,I40,I29,I18I7=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$9)),"")
J7,J40,J29,J18J7=IF(K7="","","9")
A8A8=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$10)),"")
B8,B41,B30,B19B8=IF(C8="","","10")
E8,E41,E30,E19E8=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$11)),"")
F8,F41,F30,F19F8=IF(G8="","","11")
I8,I41,I30,I19I8=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$12)),"")
J8,J41,J30,J19J8=IF(K8="","","12")
A9,A42,A31,A20A9=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$13)),"")
B9,B42,B31,B20B9=IF(C9="","","13")
E9,E42,E31,E20E9=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$14)),"")
F9,F42,F31,F20F9=IF(G9="","","14")
I9,I42,I31,I20I9=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$15)),"")
J9,J42,J31,J20J9=IF(K9="","","15")
A10,A43,A32,A21A10=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$16)),"")
B10,B43,B32,B21B10=IF(C10="","","16")
E10,E43,E32,E21E10=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$17)),"")
F10,F43,F32,F21F10=IF(G10="","","17")
I10I10=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H3,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C3,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$18)),"")
J10,J43,J32,J21J10=IF(K10="","","18")
A14,A36,A25A14=A3+1
L14,L36,L25L14=L3+TIME(0,L15,0)+3
L15,L37,L26L15=((6-COUNTIF(C5:C10,""))*$M$4)+((6-COUNTIF(G5:G10,""))*$M$4)+(6-COUNTIF(K5:K10,""))*$M$4+$M$5+L12
A19,A41,A30A19=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H14,OFFSET(Submissions!$A$2:$A$500,0,MATCH($C14,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW($A$10)),"")
I21,I43,I32I21=IFERROR(SMALL(IF(ISNUMBER(SEARCH($H14,OFFSET(Submissions!$A$2:$A$140,0,MATCH($C14,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$140)),ROW($A$18)),"")
M36M36=M23+1
X36X36=X23+TIME(0,X37,0)+3
X37X37=((6-COUNTIF(O25:O30,""))*$M$4)+((6-COUNTIF(S25:S30,""))*$M$4)+(6-COUNTIF(W25:W30,""))*$M$4+$M$5+X34
M38M38=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$1)),"")
O38,S38,W38,O41,S41,W41O38=IFERROR(INDEX(Submissions!$B:$B,M38),"")
P38,T38,X38,P41,T41,X41P38=IFERROR(INDEX(Submissions!$C:$C,M38),"")
Q38Q38=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$2)),"")
U38U38=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$3)),"")
M39M39=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$4)),"")
N39:N40,R39:R40,V39:V40,N42:N43,R42:R43,V42:V43N39=IF(O39="","","&")
O39,S39,W39,O42,S42,W42O39=IFERROR(INDEX(Submissions!$D:$D,M38),"")
P39,T39,X39,P42,T42,X42P39=IFERROR(INDEX(Submissions!$E:$E,M38),"")
Q39Q39=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$5)),"")
U39U39=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$6)),"")
M40M40=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$7)),"")
O40,S40,W40,O43,S43,W43O40=IFERROR(INDEX(Submissions!$F:$F,M38),"")
P40,T40,X40,P43,T43,X43P40=IFERROR(INDEX(Submissions!$G:$G,M38),"")
Q40Q40=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$8)),"")
U40U40=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$9)),"")
M41M41=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$10)),"")
Q41Q41=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$11)),"")
U41U41=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$12)),"")
M42M42=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$13)),"")
Q42Q42=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$14)),"")
U42U42=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$15)),"")
M43M43=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$16)),"")
Q43Q43=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$500,0,MATCH(O36,Submissions!$1:$1,0)-1))),ROW(Submissions!$A$2:$A$500)),ROW(M$17)),"")
U43U43=IFERROR(SMALL(IF(ISNUMBER(SEARCH(T36,OFFSET(Submissions!$A$2:$A$140,0,MATCH(O36,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
C25:G25List=$N$4:$N$29
H25:K25List=$Q$4:$Q$25
C14:G14List=$N$4:$N$29
H14:K14List=$Q$4:$Q$25
C36:G36List=$N$4:$N$29
H36:K36List=$Q$4:$Q$25
C3:G3List=$N$4:$N$29
H3:K3List=$Q$4:$Q$25
O36:S36List=$N$4:$N$29
T36:W36List=$Q$4:$Q$25
 

Nigeljones

New Member
Joined
Apr 26, 2013
Messages
26
Office Version
  1. 365
Platform
  1. Windows
That last mini sheet matches up better with the submissions sheet. I appreciate your help.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,391
Office Version
  1. 2016
Platform
  1. Windows
Nigel,

Just to be clear, there's no function to execute a different formula in a different part of the sheet. The only way is to work around the challenge with logic in the formulae.

Your second versions of the sheets caught me out but I've added the rows, then reapplied the CSE to get the curly brackets (as XL2BB doesn't copy them) but I think I've got your sheet now.

I don't understand what it does but I see how the formulae work. So here's the $64,000 question: Which cell changes to trigger the A51 to X56 formula changes and what changes are to be made in the formulae?

NigelJones-V2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
48Section2Start TimeSectionStart Time
49511/u ImprovisationsModern/Lyrical9:12 AM29/U Duos and TriosJazz12:06 AM
5033
51181LexieMustafa362SiennaSpence373SiennaSpence681ChelseaSantana        
52394MadilynLake odea415JessicaRobinson846NathanielDigges-Lagula &00        
53             &00        
54                        
55                        
56                        
57Results: 1st………………..2nd………………..3rd………………..HC………………..Results: 1st………………..2nd………………..3rd………………..HC………………..
Program
Cell Formulas
RangeFormula
E48E48=LEN(A51)
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
A51A51=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$1)),"")
B51,N51B51=IF(C51="","","1")
C51:D56C51=IFERROR(INDEX(Submissions!B:B,$A51),"")
E51E51=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)),"")
F51,R51F51=IF(G51="","","2")
G51:H56G51=IFERROR(INDEX(Submissions!B:B,$E51),"")
I51I51=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)),"")
J51,V51J51=IF(K51="","","3")
K54:L56,K51:K53K51=IFERROR(INDEX(Submissions!B:B,$I51),"")
L51:L53L51=IFERROR(INDEX(Submissions!C:C,$I51),"")
M51M51=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)),"")
O51,W54,S54,O54,W51,S51O51=IFERROR(INDEX(Submissions!$B:$B,M51),"")
P51,X54,T54,P54,X51,T51P51=IFERROR(INDEX(Submissions!$C:$C,M51),"")
Q51Q51=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)),"")
U51U51=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)),"")
A52A52=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)),"")
B52,N54B52=IF(C52="","","4")
E52E52=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)),"")
F52,R54F52=IF(G52="","","5")
I52I52=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)),"")
J52,V54J52=IF(K52="","","6")
M52M52=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)),"")
V55:V56,R55:R56,N55:N56,N52:N53,V52:V53,R52:R53N52=IF(O52="","","&")
O52,W55,S55,O55,W52,S52O52=IFERROR(INDEX(Submissions!$Q:$Q,M51),"")
P52,X55,T55,P55,X52,T52P52=IFERROR(INDEX(Submissions!$R:$R,M51),"")
Q52Q52=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)),"")
U52U52=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)),"")
A53A53=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)),"")
B53B53=IF(C53="","","7")
E53E53=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)),"")
F53F53=IF(G53="","","8")
I53I53=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)),"")
J53J53=IF(K53="","","9")
M53M53=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)),"")
O53,W56,S56,O56,W53,S53O53=IFERROR(INDEX(Submissions!$U:$U,M51),"")
P53,X56,T56,P56,X53,T53P53=IFERROR(INDEX(Submissions!$V:$V,M51),"")
Q53Q53=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)),"")
U53U53=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)),"")
A54A54=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)),"")
B54B54=IF(C54="","","10")
E54E54=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)),"")
F54F54=IF(G54="","","11")
I54I54=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)),"")
J54J54=IF(K54="","","12")
M54M54=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)),"")
Q54Q54=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)),"")
U54U54=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)),"")
A55A55=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)),"")
B55B55=IF(C55="","","13")
E55E55=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)),"")
F55F55=IF(G55="","","14")
I55I55=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)),"")
J55J55=IF(K55="","","15")
M55M55=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)),"")
Q55Q55=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)),"")
U55U55=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)),"")
A56A56=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)),"")
B56B56=IF(C56="","","16")
E56E56=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)),"")
F56F56=IF(G56="","","17")
I56I56=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)),"")
J56J56=IF(K56="","","18")
M56M56=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)),"")
Q56Q56=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)),"")
U56U56=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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,596
Messages
5,625,721
Members
416,130
Latest member
galgozzi

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
Top