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

Zogmaster

New Member
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Replies
1
Views
210
Replies
6
Views
396
Replies
0
Views
263
Replies
8
Views
307
Replies
2
Views
526

1,203,460
Messages
6,055,554
Members
444,796
Latest member
18ecooley

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.

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

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