Crcket stats input and reading mutiple cells

Hello all,

I'm currently in the middle of creating a team nomination form for my cricket district in Sydney. Now some of the problems haved been solved using VLOOKUP, but now I've hit a wall;

When i have entered in a player's ID number, everything works well until i noticed that using MAXIFS from my data doesn't work, particularly if the player has stats across multiple grades/division. It might have highest runs from 6th grade, yet more matches in 5th grade. I need one set of matching data when entering in the grade.

What i need is for a formula (with in One Cell) that'll read the table, recognise the ID number, THEN their grade/division, THEN from the stats will match the row that all this comes from and will add the Matches they played in that grade/division.

the same process will apply for innings, runs, wickets, from the stats.

Lumpy115

Morning Ford,

Thank you, attached is dummy file and have left some notes and failed formula attempts

Ford,

have colour coded a few groups of cells with some detail to what i would like. The sample answers you're looking for will be colour
coded in the attachment on this reply, as well as some other colour coded details.

will be on for a bit should you be around

Lumpy

shukla,

thanks for supplying the above formulas, but for what im hoping to read is when stats are entered, it first needs to read either the player ID or NAME, AND what Grade they played in, then it enters in that correct stats. So what I'm after is that it reads the row of information in cells A9 or B9, then I9. Then it matches the row that both (or all three) come from on the right table and inputs the data for matches, inngs, runs, etc.

So as a quick eg, If i have 'player 1' in ROW 9 (in left table) their 5th grade, I want the stats to match. I'd want all the information from ROW 3 on right table. Because it meets the required ID number (AC3), name (AD3) and grade (AU3). Then so on and so on, because ill have 1500 rows of data in right table so i need it to read every column and row.

Morning all,

Does anyone else have an option or suggestion for me please? While Shukla providing something, it does not work for me.

Lumpy

Hi FD,

if i need to adjust the grade the player played in, the stats won't change according. I've placed the formulas Skukla provided in the cells and while it in part works, I need to be able to change the grades where possible rather than it pre filling, and when it's entered, then it puts the right stats in.

Can you provide the dummy file when you did it please, and if possible, fill the table for rows 9-20 in the table please?

Lumpy

Could i use this formula in the picture attached? I've tried it in my spreadsheet and i cannot figure out why it won't read
it as it does in the example at the top of the attachment.

In the example and what I've tried is in the table is for cell L9;

=INDEX(AH3:AH9,MATCH(1,(B9=AC3:AC9)*(C9=AD3:AD9)*(I9=AE3:AE9),0))

AH3-9 is matches played

B9= player id# (AC3-9= players id#'s)

C9= player name (AD3-9= player names)

I9=Grade (AE3-9= grades for each player)

The attachment ticks boxes between words and numbers that i need and is matching the requirements to input the correct number.

yes, this has worked, and when i changed the grade, it updates according.

Thank you all, I've adjusted the formulas to match the innings and runs, and so far they are all working according. To Ford, Shakla, FD, Jete, and kvsrinivasamurthy, thanks you for taking a little time to assist.

What should be a final question, Am i able to use Data Validation to create a drop down list just for the grades each player has played for? Eg, Players 1 (has either '5th' or '6th' as an option. Eg2 player 2 (has either '1st' or '2nd' as an option?

thanks mate,

I'll give it a try in a few hours.

Lumpy

Thank you again all, enjoy your weekends. Lumpy