I have a spreadsheet with 6 worksheets. The worksheet quantity will not change. Each worksheet can only name a person once. But each person can be on multiple worksheets. There is one column that has numbers in it ranging from -10 to 10. I want to create a rankings worksheet (a 7th) that has a list of each person on the 6 other worksheets. This worksheet needs to search the largest value from the 6 other worksheets for each person. In addition, it needs to return that person's role that had the largest value.
Basically since my range includes negative values, the max function will not work.
=MAX(MAXIFS(PosA[Grade],PosA[Name],[@Name]), MAXIFS(PosB[Grade],PosB[Name],[@Name]), MAXIFS(PosC[Grade],PosC[Name],[@Name]), MAXIFS(PosD[Grade],PosD[Name],[@Name]), MAXIFS(PosE[Grade],PosE[Name],[@Name]), MAXIFS(PosF[Grade],PosF[Name],[@Name]))
To get the person's position where the max value came from - some sort of index or match seems appropriate, but I've struggled to figure out where to start.
Basically since my range includes negative values, the max function will not work.
=MAX(MAXIFS(PosA[Grade],PosA[Name],[@Name]), MAXIFS(PosB[Grade],PosB[Name],[@Name]), MAXIFS(PosC[Grade],PosC[Name],[@Name]), MAXIFS(PosD[Grade],PosD[Name],[@Name]), MAXIFS(PosE[Grade],PosE[Name],[@Name]), MAXIFS(PosF[Grade],PosF[Name],[@Name]))
To get the person's position where the max value came from - some sort of index or match seems appropriate, but I've struggled to figure out where to start.