The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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?

Thanks in advance

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?

Thanks in advance

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.

Select your new worksheet from the left pane by double clicking

Insert > module

paste your code here.

```
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
```

```
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"??

--------------------------

You don't need VBA and a formula will be easier to support and more efficient. Using your original criteria:

1st place = 100 points, 2nd = 75 points, 3rd = 50 points, 4th = 25 points and 5th (or 6th/7th/8th etc.) are 10 points

And looks like your code is attempting to do that (by the way, you need END IF statements after your ELSE statements!), so for each judge, you get 5, then 10, 15, 20 max for 1,2,3,4,>4 judges

For place, we have the calculation:

IF(ISERROR(VLOOKUP(B3,{1,1

if we take the error checking out (if entry is <> 1,2,3,4,5, or > 5), then the formula looks like:

VLOOKUP(B3,{1,100;2,75;3,5

So, its a simple vlookup, with a two dimensional array in the formula:

{1,100; etc.... means the first row is 1 in column 1 and 2 in column 2, etc.

For Judge, the calculation is:

IF(ISERROR(VLOOKUP(C3,{1,5

So, for score adding place and judge together, we get:

=IF(ISERROR(VLOOKUP(B3,{1,

I don't know where your attendance and participation cells are, but let's say they are in each competition block, so the first would be column D and E respectively. To get the entire score, then we add these to parameters in with an IF statement and then add it all up!

=IF(D3="y",10,0)+IF(E3="y"

Now, you can copy that to all the other result cells.

See attached demonstration workbook.

Cheers,

Dave

scoring-r1.xls

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

Dave

scoring-r2.xls

=IF(D3="y",15,0)+IF(E3="y"

See attached.

Dave

scoring-r3.xls

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

So I was going in the right direction with the formula, but it was a little above my head.

Cheers

Cheers,

Dave

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

>>PS. how do you make it so the heading rows don't move when you scroll down?

Use Freeze Panes

http://spreadsheets.about.com/od/exceltools/ss/81027freezepane.htm

>>And how do you stop people from accidentally erasing or modifying the formula in the cells?

The simplest way is to lock the cells you don't want them messing with and then protect the sheet.

If you select all cells, then right click, format cells, protection - the default is locked. So, once you've confirmed that, you go select all cells that they CAN edit, right click and set protection to not locked (uncheck that). Then right click your sheet tab and password protect the sheet.

http://www.dummies.com/how-to/content/protecting-cell-data-in-excel-2007.html

See attached formulas incorporated. I turned your freeze panes on (selected cell B3, then Ribbon->View->Freeze panes) - now you can scroll around and keep the headers at the left/top. I also unprotected the data entry area on the first 30 rows and password protected your sheet - password "password".

See attached.

Enjoy!

Dave

FTBA.xlsm