Unique values from multiple sheets Here\'s what I have so far. I\'ve got 4 sheet

Question

Unique values from multiple sheets

Here's what I have so far. I've got 4 sheets; "October", "November", "January", and "February"; which will contain scores for teams and quizzers for a competition season. Each of those sheets is set up the same way. A2:A38 will contain team names, B2:B38 will contain the quizzer names, columns C:K will contain points for each round, Column L gives total points for the day, Column M gives a rank based on total points for the day.

Below that, starting in row 41, column A, I have formulas (=IFERROR(INDEX($A$3:$A38,MATCH(0,INDEX(COUNTIF($A$40:A40,$A$3:$A$38),0,0),0)),"")) to pull a unique list of team names from the cells above. Column B totals the total points from each quizzer (using sumproduct), columns C:K will be W or L for Win/Loss, Columns L and M count the W's and L's for the team and then there are columns to rank the wins and total points.

Then I have an "End of Year" worksheet. Here's what I want that to include: Columns A and B will include an unduplicated list of quizzers that appear on any of the four monthly worksheets along with the team they are on. Columns C, D, E and F will include the number of points earned, if any, by that quizzer in the individual months (I would think I could use a vlookup for this). Finally columns G and F will calculate total points for the season and rank (these are easy-peasy once the rest is done).

Below the quizzer information, I'll pull team information using the same formulas I use on the individual worksheets (from paragraph 2 above).

I need help on the third paragraph. I need formulas to pull a unique list of quizzer names.

What I've tried: I tried, using columns A and B to just reflect the names entered on the individual sheets (=October!b3) with an if to leave it blank if there's nothing in the monthly sheet on that row and then in column C used the same formula that I used for the team names up the second paragraph. However, it did not list any of the names when I used that (and, yes, I did do Ctrl+Shift+enter for the Array formula).

I would not be opposed to a macro that would automatically rrun only when the quiuzzer names columns are edited if possible (I wouldn't want it to run anytime someething is edited as that would slow things down considerably most likely).

Any help is appreciated!!!!

Explanation / Answer

Here's a rather resource intensive formula solution for listing unique values in A3:A38 from multiple sheets. First, let's assume that Sheet1!A2:A5 contains the sheet names (ie. October, November, January, and February). Then define the following names...

Ribbon > Formulas > Defined Names > Name Manager > New

Name: N

Refers to: 36

(The number 36 represents the number of rows per sheet.)

Click OK

Click New

Name: S

Refers to:

=ROW(INDIRECT("1:"&(N*ROWS('Sheeet1'!$A$2:$A$5))))

Click OK

Click New

Name: ColA

Refers toi:

=T(INDIRECT("'"&T(OFFSET('Sheet1'!$A$2:$A$5,INT((S-1)/N),,1))&"'!A"&MOD((S-1),N)+3))

(The number 3 at the end of the formula specifies that the data for each sheet starts at Row 3.)

Click OK

Click Close

Then try......

A41, copieed down:

=IFERROR(INDEX(ColA,MATCH(0,INDEX(COUNTIF(A$40:A40,ColA),0,0),0)),"")

Hope this helps!!!

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.