Hi Everyone,
I am working on a spreadsheet that is used to collect data and produce interesting charts and information for when you are playing a popular computer game called Rome 2 Total War. If you have never heard of it, it is a game a lot like RISK in which you raise Armies and try to take over the world.
For the most part the spreadsheet is done. However, to make it as user friendly and inviting to many people who would normally shy away from doing something on excel, I think the easiest way to ensure that is to create a sort of "Master Page" at the start of the spreadsheet that is used to enter in your information after every battle and with the click of a button it adds that information for you to the appropriate cells within the workbook.
I had a similar project about a year ago. I wish I had picked my helpers brain a bit more, but he at least taught me to give precise instructions so please excuse the wall of text and reference images.
I am sure this will probably amount to 30-60 minutes of work, so I am willing to compensate $30 if you so wish.
________________________________________________
Here is a snap shot of the main control sheet. It is from this sheet that the user will type in the details of his battles as he plays Rome 2 Total War so that he does not ever have to attempt to put it anywhere else nor should he. What this sheet needs to do is run a script which takes what the user just entered and puts them in their correct places within the workbook so all of the charts I have on the Dashboard sheet can get their information from.
inputsheet.jpg
This sheet (Main Control sheet) will need THREE Scripts made. The first (and longest) will be for the farthest left column where you input details about your battle. Everything entered in this Section (albeight one cell that gets changed on the Main Control sheet) gets added to ROW2 of the "Battle History" tab. *Note that as each entry occurs, the newest entry will remain in ROW2 while the old entries get moved down. Their cell reference on where they go are in the image below along with a example to show how I need it to look like for my charts:
1st SCRIPT NEEDED
battle_input_area.jpg
And this is what it should look like with the example image above: (Ignore the cells where there is data like I2 and K2, those are just formulas for that column)
example_output.jpg
Lastly, for that first script, the last thing I need to happen when the user hits the "Enter Battle" button to submit his data, I also need the percentage data in F11 moved to J11 in the Main Control page. Unfortunately it cannot just be a simple formula like =F11 because if you notice the conditioning arrow, I want that to show the user if his Kill to Death Ratio improved or not since entering his last battle. So the concept for this remains the same as the image above, the only difference is that it changes a cell not in the "Battle History" sheet, but the same sheet it is already on. So, if you take my example on the first image of this post in which the players K/D Ratio was 44.88%, then say he enters another battle and his K/D Ratio% drops, it should look like this.
percent.jpg
2nd SCRIPT NEEDED
The next Script that needs made is for the upper middle area for Adding Armies. This script needs to take the information provided after hitting the "Add Army" macro (Army Name, Region Created, and Year Created) and add that to H3 (Army Name) J3 (Region Created) and I3 (Region Created). However, unlike the battle history sheet in which the newer entries remained at the top, this script needs to list them in the next available row below.
add_army.jpg
3rd SCRIPT NEEDED
The last script needed deals with the declaring war in the bottom middle. This script needs to control when a war is started and ended. The started part should be easy by now since it is the same thing you have done for everything else so far, however, the "End War" part may be a little difficult (or impossible) because it needs to find the faction first and then ensure that it still remains a blank cell in column D of whatever row it is on before it can enter in the date the war ended. In addition, keep in mind that there can will be times in which a player will be in a on again/off again war with the same faction two, three, or many more times.
****If you are fine with the $30 for the first 2 scripts, but are turning away from this request due to this last script, we can leave it out. But if you already know how you would perform this in your head already then it is appreciated!****
In the image below, it includes an example and what the output would be had that example been used:
(Note that I ran out of room for attachments, so please refer to the links)
http://the3rdmarines.com/images/rome...e_example1.jpg
http://the3rdmarines.com/images/rome...e_example2.jpg
The references for where the data needs to go is in this image. The data is changed on the tab called "War Phases". Again, disregard other columns that are on there. Those are formulas. Also, newest entries need to be shown from newest to oldest / top down.
http://the3rdmarines.com/images/rome/war_references.jpg
And that is it. Again, I am more than happy to pay $30 for the trouble. This worksheet will put a smile on a lot of peoples faces hopefully. Even though its finished, having someone help make these macros will make it very helpful to people who have never used excel before.
Here is the File: Rome 2 Campaign Logger
Bookmarks