A learning experience

The attached Fishing Comp workbook consists of three sheets, namely Results, Standards and Data.
The Results worksheet contains a leaderboard and 4 active x command buttons. Whilst the leaderboard is relatively dynamic, it needs to be more so to be better functional. At this stage its manually designed.

The Standards worksheet contains a series of tables which govern how the fishing comps are to be run. The two important ones are the SOFish table which is used to determine which fish of the 10 targeted fish are important to the active fishing comp, and how many points are assigned to which fish.


The Fees table is used to determine the entry fee for each team. Fees are applied on a per comp basis and will be changed from time to time


The Data worksheet is to be used to store the relevant comp data and is only a stop gap measure as the club intends to use Access to store the details as their experience grows.
As this is a learning experience a series of questions will be developed as the project develops, so any assistance offered will be greatly received.
A userform has been designed to collect data for those teams and is called by the cmdButton “Entry”. Using this as our first lesson, how do we get this to both the Leaderboard and to the Data sheet? A new leaderboard will gain in both rows as more entries are entered, and the columns dedicated to each fish targeted in the active comp will change as will the column headings per fish.

1. I took a shot at your questions in my markup (attached). Changes / suggestions are high lighted in orange

2. However, you might consider using something like your Data sheet to capture your 'raw' data with Excel's Data Validation used to restrict entered values. Your 3 tables as well as numerical limts would easily work.

3. Then for the Leaderboard formatting, calculating, etc. --- that could all be done by as macro. I like to seperate the data capturing from the data analysis and the 'dashboard' for management

4. The formulas in the 'Leaderboard' sheet (e.g. =SUM(C2:C12) ) would not have to be adjusted when the list of the types of fish change, different number of boats, etc. since the macro would be smart enough to use the raw data and the control tables


Paul

Trying to post a couple of thought and a markup of your file, but the system says I aready did. I don't see it so you might get 2 from me :beerchug:

Paul

Tried to post answer again (#3)

Wonder if there's system problems??

Paul

Trying to answer some your questions, but the system seems to be ignoring me :dunno

Says my file is already attached

Paul

See if the file makes it this time

Paul

Thanks Paul. Whilst your concept is good,I was looking at a slightly different outcome.

I had hoped to make the leaderboard very dynamic. By adding additional rows as more entries ( boats) are received and as the type and/or number of targeted fish for the competition changes.

Determination of legal fish for the competition is by using only those fish in the table where their value is greater than zero. The leaderboard then only shows enough columns and headings to reflect these fish. The column range could be as little as C:C or a maximum of C:L

In the Excel subforum the following section of code was provided, by Mancubus to pick up the correct number of columns.
[vba]
Private Sub InsertMultiCol()
Dim insCol As Long
insCol = Range("Num_Fish").Value
With Worksheets("Results").Range("C1").Resize(, insCol).EntireColumn.Insert
End With
End Sub
[/vba]
Bob kindly suggested the following code to pick up the names of the fish to place as column headers.
[vba]
Private Sub GrabFish()
Dim cell As Range
Dim col As Long
col = 3
With Worksheets("Standards").Range("SO_Fish")
For Each cell In .Columns(4).Cells
If cell.Value > 0 Then
Worksheets("Results").Cells(1, col).Value = cell.Offset(0, -3).Value
col = col + 1
End If
Next cell
End With
End Sub
[/vba]
So now I'm left with the issue of trying to calculate the total fish caught per team, the total points earned per team and their rankings.

How do I sum a dynamic range? I obviously need to know how many columns are involved. The Num_Fish value tells me this, but I don't know to apply this in a formula.

Points gained. In the manually constructed formula I used a series of lookups to determine the points per fish type, but have no idea how to do this on a dynamic range.

Ranking of the teams is not a problem.

When you do get it dynamically updating, could you post the final result?

I'd like to see how you did it

Paul

Hi Ted,

Quote:

---

Originally Posted by Aussiebear

The attached Fishing Comp workbook consists of three sheets, namely Results, Standards and Data.

The Results worksheet contains a leaderboard and 4 active x command buttons. Whilst the leaderboard is relatively dynamic, it needs to be more so to be better functional. At this stage its manually designed.

---

Quote:

---

Originally Posted by Aussiebear

I had hoped to make the leaderboard very dynamic. By adding additional rows as more entries ( boats) are received and as the type and/or number of targeted fish for the competition changes.

---

I am not yet understanding some of the parameters we are looking at. For instance, let us say we are entering data for the 'Easter 3 Day' competition. I understand that we would need to add rows to the 'Leader Board' if there were twelve boats entered, but am unclear as to when we would know that we needed extra rows. I say this as you were asking about inserting columns for each fish type.

So... Are we creating a new workbook, or maybe a new 'Results' sheet for each competition?

Also, you mention changing the type of targeted fish. Does this mean selecting which fish count once per competetion, or, could the type of fish being sought change on day two of a three day competition?

Quote:

---

Originally Posted by Aussiebear

A userform has been designed to collect data for those teams and is called by the cmdButton “Entry”. Using this as our first lesson, how do we get this to both the Leaderboard and to the Data sheet?

---

At least so far, I only see 'Captain' as being in both the data entered in the userform and in the 'Results' sheet. Before figuring out how to plunk what data where in the sheet(s), at least for the poor blond guy, could we clarify as to whether we are creating a new/fresh 'Leader Board' for each competition, for each day of a competition, or??? Also - maybe points are kept only for the length of a competition. But if points are tracked for the year (season/quarter/whatever) and there's any type of grand prize or trophy, what is a team's unique identifier, the captain? the boat's name?

You mention that all the collected data is headed for a database at some point, so I am thinking that whatever defines a "Team" is important, as would be a unique identifier.

Could you explain how this all works? By example, let us say we are entering the 'Family Day' competition. Is there a 'Family Day' competition at all of the locations, or is each competition at just one assigned river?

Mark

Hey Mark -- we believed you the first 5 or 6 times :devil2:

Paul

ROFL......

Mark, the "organisers" of the fishing comps decide where to fish, what type of comp it is, what type of fish to target and the entry fees. They do this by using the data on the Standards sheet.

The Standards sheet consists of tables, (Locations, Competitions, SO_Fish, & Fees). SO_Fish table consists of ten common fish types to the area, and will have two editable columns when completed, these being Length and Points. Only those fish meeting the minimum length or above, and only the targeted fish will have a points value greater than zero.

The Data sheet will be used to record on which days comps were held, where they were held, what fish were targeted, how many were caught, who was fishing at each comp, the ranking achieved and what fees were applied. The data sheet is simply a holding point until I get to understand how to transfer the information over to an Access database (yet to be created).

The Results sheet is being used to show the leaderboard for the current comp. It needs to add rows per new entry, by showing the Entry Number, who the Captain of the boat is, how many persons on board (POB), the total catch for each targeted fish type, the calculated total points gained and the ranking achieved by each team in the comp. I will also need to calculate the individual totals per fish type in each fish comp

This is a complex project for me, as I understand only some of the object layer in Excel, very little of the VBA potential, and very little of Access. Some will no doubt suggest that this should be built within Access initially and that possibly correct, but anyway its a learning curve. I have limited time to apply to the project and will be plodding along.

At this stage, I have changed the leaderboard layout so that the added fish columns now come after the ranking column, and am currently trying to work out how to calculate the total points per team given that I need to lookup each fish type for the points on offer and multiply that by the number of each fish caught and then sum the points gained per fish type. This value then helps with determining the team ranking in the comp. Some comps will have just one fish type being targeted, but there could also be all ten fish being targeted in another. Hence the need for the leaderboard to be fully dynamic.

Fees calculation is taken from the Fees table and is calculated by Fee per boat plus fee per person on board. The fees may be changed per comp type ( Example on a family day comp, it may just be a per boat fee only). The club donates some of the money back to re-stocking programs so we need to find when & where the money is generated, and then apportion income back to different river systems.

I need to store fish catches per fish type and where they are being caught from, so that over time we can determine if the numbers are declining, and whether these locations need to be changed. You are probably all screaming "Access, Acccess, Access" by now.....

Did you get it dynamically updating?

If so, can you post it?

I thought Mark had a number of posts here at one tine, but the count still = 10

Paul