Multi Dynamic Analysis of data to locate cell via text reference then use the information either side of that cell

Zogmaster

New Member
Joined
Mar 25, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Watch MrExcel Video

Forum statistics

Threads
1,130,327
Messages
5,641,541
Members
417,218
Latest member
dchapman17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top