Hi gurus,
I have a game that I play with a friend and we thought it would be great to have a playbook using the data from each battle. That data is hard coded and contains all the information needed to populate the fields in the playbook, which for ease I have placed into the same sheet. I later copy it out and add some formatting but my question here is one of cell calculation.
Here is my spreadsheet. It is on my secure and very safe personal cloud: Warriors Quest - 24-25 March 2021 - War Statistics - Blow by Blow - HARD CODED SOLUTION.xlsx
The TAB I would like some help on is "Wat Attack Playbook"
I have hard coded all the cells as you can see. There are only 2 teams, each with players that make up their team. Players essentially attack other players in the opposing team. My team is called Warriors Quest and the playbook I have created reflects each battle, their attack number, who they were dueling against and the resultant points gained from that battle.
Data entered manually is in COLUMNS A - L.
Total Attacks in N3:N12 walks down COLUMN C for the match to the name in J3:J12 and results in the total number counted for each name respectively - These are the Warriors Quest team names
Total Attacks in N17:N26 walks down COLUMN C for the match to the name in J17:J26
and results in the total number counted for each name respectively - These are the opposing teams team names.
COLUMNS A - D are all attackers information
You will see in COLUMN A numbers from 1 - 6 under the heading "Atk #". Each player is allowed up to a maximum of 6 attacks each. Each attack can com eat any time so each players Atk # is inserted as their attack completes.
COLUMN C contains the attacking players power rating
COLUMN D contains the attackers score in terms of victory points. I have coloured the information for Warriors Quest attacks in black text and for the opposition attacks in red. It just made it easier for me to differentiate between the two.
COLUMN E - H are all defending players information
You will see in COLUMN H the total number of defenses made. This can by any number as multiple players can attack the same player. In this battle there were some players who endured 10 attacks.
COLUMN G is the corresponding power rating for the player being attacked.
COLUMN F is the Opponent (the one being attacked) name listed in order of when the attack took place.
COLUMN E is the number of points awarded and lost on the attack.
I have hard coded the results, but as you can imagine this takes a lot of time and is prone to typo errors etc. I don't need to use a database as there is really only this one sheet worth of data so ideally I would really love to be able to automate the cell populations in COLUMNS P - AL and also COLUMNS AR - CD
I have split the Warriors Quest results for both attacks and defenses from the opposition attacks and defenses with the Warriors Quest information between ROWS 1 - 12 and the opposition information between ROWS 14 - 26.
If anyone is able to help I would be eternally grateful.
Should you need any further information please do not hesitate to ask. The challenge for me is I do not know how to locate a cell based on the text search, and then gran the information I need from either side of it.
Many thanks,
Paul
I have a game that I play with a friend and we thought it would be great to have a playbook using the data from each battle. That data is hard coded and contains all the information needed to populate the fields in the playbook, which for ease I have placed into the same sheet. I later copy it out and add some formatting but my question here is one of cell calculation.
Here is my spreadsheet. It is on my secure and very safe personal cloud: Warriors Quest - 24-25 March 2021 - War Statistics - Blow by Blow - HARD CODED SOLUTION.xlsx
The TAB I would like some help on is "Wat Attack Playbook"
I have hard coded all the cells as you can see. There are only 2 teams, each with players that make up their team. Players essentially attack other players in the opposing team. My team is called Warriors Quest and the playbook I have created reflects each battle, their attack number, who they were dueling against and the resultant points gained from that battle.
Data entered manually is in COLUMNS A - L.
Total Attacks in N3:N12 walks down COLUMN C for the match to the name in J3:J12 and results in the total number counted for each name respectively - These are the Warriors Quest team names
Total Attacks in N17:N26 walks down COLUMN C for the match to the name in J17:J26
and results in the total number counted for each name respectively - These are the opposing teams team names.
COLUMNS A - D are all attackers information
You will see in COLUMN A numbers from 1 - 6 under the heading "Atk #". Each player is allowed up to a maximum of 6 attacks each. Each attack can com eat any time so each players Atk # is inserted as their attack completes.
COLUMN C contains the attacking players power rating
COLUMN D contains the attackers score in terms of victory points. I have coloured the information for Warriors Quest attacks in black text and for the opposition attacks in red. It just made it easier for me to differentiate between the two.
COLUMN E - H are all defending players information
You will see in COLUMN H the total number of defenses made. This can by any number as multiple players can attack the same player. In this battle there were some players who endured 10 attacks.
COLUMN G is the corresponding power rating for the player being attacked.
COLUMN F is the Opponent (the one being attacked) name listed in order of when the attack took place.
COLUMN E is the number of points awarded and lost on the attack.
I have hard coded the results, but as you can imagine this takes a lot of time and is prone to typo errors etc. I don't need to use a database as there is really only this one sheet worth of data so ideally I would really love to be able to automate the cell populations in COLUMNS P - AL and also COLUMNS AR - CD
I have split the Warriors Quest results for both attacks and defenses from the opposition attacks and defenses with the Warriors Quest information between ROWS 1 - 12 and the opposition information between ROWS 14 - 26.
If anyone is able to help I would be eternally grateful.
Should you need any further information please do not hesitate to ask. The challenge for me is I do not know how to locate a cell based on the text search, and then gran the information I need from either side of it.
Many thanks,
Paul
