I need to create a new spreadsheet based on a rival club's results spreadsheet.
I'm looking at the other club's spreadsheet and I can see a formula, but I don't know where it is getting some of the data from or where it is defined.
So, as you can see G3 is the addition of F3 and E3.  But E3 and F3 are numbers that are 1, 2, 3, 4 or 5, and what each value is worth must be defined somewhere else.
Eg:  1st place = 100 points, 2nd = 75 points, 3rd = 50 points, 4th = 25 points and 5th (or 6th/7th/8th etc.) are 10 points.   Each heat judge is worth 10 additional points and so on, to give the final "round" score for the competition.  These are all added at the end of the season and placings are awarded.

Where could the data values of E3 and F3 be defined?  Is there any way to display all formulas or hidden worksheets or something?

Microsoft Excel

Last Comment
dlmille

8/22/2022 - Mon
Saqib Husain

This is a user defined function (UDF).

To see this press Alt-F11. On the left of the VBA window there is a pane called Project. There you will see your file as a main entry. This will have Module as a sub entry and there will be one or more further subentries under Module. This function is probably in one of these subentries. Double click the subentry. You will see code. Search for a line which contains

Function competition(...

This is where the function is defined.
Reece

ok, cool.

The sheet I want to create is a little more elaborate... see below image.
How do add this "UDF" into my spreadsheet and how do I code it to work like my "Round Points Key"??

Saqib Husain

Copy the VBA code for the udf from the other worksheet
Select your new worksheet from the left pane by double clicking
Insert > module
Reece

This is the code from the other spreadsheet...
``````Option Explicit

Function competition(place, judges) As Integer

Dim score As Integer
Dim tally As Integer

If place = 1 Then
score = 100
ElseIf place = 2 Then
score = 80
ElseIf place = 3 Then
score = 70
ElseIf place = 4 Then
score = 60
ElseIf place = 5 Then
score = 40
ElseIf place = 7 Then
score = 30
ElseIf place = 9 Then
score = 20
ElseIf place = 10 Then
score = 20
ElseIf place > 10 Then
score = 10
Else
score = 0
End If
If judges = 1 Then
tally = 5
ElseIf judges = 2 Then
tally = 10
ElseIf judges >= 3 Then
tally = 15
Else
tally = 0
End If

competition = score + tally

End Function
``````
Reece

i've got this so far...
``````Option Explicit

Function competition(attendance, participation, placed, judged) As Integer

Dim attended As Integer
Dim participated As Integer
Dim score As Integer
Dim tally As Integer

If attendance = "y" Then
attended = 10
Else
attended = 0

If participation = "y" Then
participated = 10
Else
participated = 0

If place = 1 Then
score = 100
ElseIf place = 2 Then
score = 75
ElseIf place = 3 Then
score = 50
ElseIf place = 4 Then
score = 25
ElseIf place = 5 Then
score = 0
ElseIf place > 5 Then
score = 0
Else
score = 0
End If
If judged = 1 Then
tally = 5
ElseIf judged = 2 Then
tally = 10
ElseIf judged = 3 Then
tally = 15
ElseIf judged = 4 Then
tally = 20
ElseIf judged > 4 Then
tally = 20
Else
tally = 0
End If

competition = attended + participated + score + tally

End Function
``````

But I think I'm getting line 1 wrong.  How do I 'declare' attendance when I need it to 'expect' a "y" or a "n"??
Reece

I got it working with the following formula, and the original UDF code...
Is there a better way I could be doing it??

SOLUTION
dlmille

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
dlmille

That formula beats the heck out of 100 lines of code, correct?  ;)

Now that you created tables on your worksheet, you can reference them (rather than embedding the array in the formula, but you'll have to get rid of the equal  sign.

If you upload your workbook in, I can correct that and put the formulas in.  I'll be around for 15 more mins.

Dave
dlmille

PS - with the scoring rules on the picture you posted, I updated the formulas.  See attached.

Dave
scoring-r2.xls
dlmille

And, here's the final formula with tables in the worksheet like you have.

=IF(D3="y",15,0)+IF(E3="y",15,0)+IF(ISERROR(VLOOKUP(B3,\$V\$9:\$W\$13,2)),0,VLOOKUP(B3,\$V\$9:\$W\$13,2))+IF(ISERROR(VLOOKUP(C3,\$V\$16:\$W\$19,2)),0,VLOOKUP(C3,\$V\$16:\$W\$19,2))

See attached.

Dave
scoring-r3.xls
Reece

one reason why not - the data in the empty workbook is highly classified and contains billions of dollars worth of financial information... no, but really, it hadn't even occurred to me to attach the spreadsheet.

I have now.

PS.  how do you make it so the heading rows don't move when you scroll down?  And how do you stop people from accidentally erasing or modifying the formula in the cells?
FTBA.xlsm
dlmille

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Reece