Hello everyone.
Please i need help with my spreadsheet that has multiple information on it.

What i want to do is to pick each years TOTAL SCORE from the yearly sheets and populate the same total scores into the appropriate columns in the cumulative sheet.

And i am supposed to get these scores by matching 2 unique values which are the NAMES, AND ORACLE NO. And if these informations are present, then it will return the total score of that individual into its appropriate year.

The name and oracle number is unique to individuals except where we probably have typographical errors else it's a unique value to all individuals.

Please anyone help me with this task as i have tried severally but all to no avail.

Thank you all.

Attached Files

done 2.xlsx (64.9 KB, 10 views)

Try:


=SUMIFS(INDIRECT("'year "&RIGHT(F$1,2)&"'!$G:$G"),INDIRECT("'year "&RIGHT(F$1,2)&"'!$B:$B"),$C2,INDIRECT("'year "&RIGHT(F$1,2)&"'!$D:$D"),$D2)

copied across and down

Thank you very much for your response.

The formula you sent worked perfectly well in the test attachment I sent at the beginning of the thread however when I used it on the actual document, it did not work and I can't explain why.

Please I need more assistance and I have also attached a copy of the actual document here

Attached Files

DATA 1.xls (483.0 KB, 10 views)

The matches must be exact matches. I noted many times you have Mr or Mrs in brackets following the names in the 2015, 2016 and 2017 but in the cumulative sheet the Mr and Mrs is in front of the names...

Review the names in the cummulative sheet, they do not exactly match the names in the years sheet. You were not consistent on the placements of the "Mrs" and "Mr". Also, if you want to be alerted of duplicated Oracle Numbers, a simple conditional formatting will do that for you.

You are very correct, the names are not consistent because I got them from different sources and I don't have control over it.

Can you please help me use oy the oracle numbers to update the cumulative sheet then?

So if there are duplicate oracles, when can sort that update manually atlease it will reduce the stress of going through all the files.

Are you okay if I replace the registration number with the name?

I'm sorry NBVC, I missed reading your second post. At any rate, Oluagbe, I removed the name in the criteria but it would be best if you add the registration number in the criteria. You change "$B:$B" to "$C:$C" (The "C" is the column number of the registration numbers in the year sheets) and the C2 to E2. C2 is the cell address of the name in the cummulative sheet while E2 is the registration number.


I've placed the conditional formatting in columns D and E. Try copying a number and pasting below it, the duplicate number should be colored.

Attached Files

Copy of DATA 1.xls (499.5 KB, 8 views)

Yes it's ok with me, in as much as it will make the job more easier.

Thank you very much.

I was thinking what if we use just one criteria which is more stable that is the oracle numbers?

This should even populate more cells with the actual scores from the yearly sheets into the cumulative sheet.

I edited NBVC's formula, removing the name in the criteria so It's just the Oracle numbers. You will notice the cells with colored background caused by the conditional formatting. You type the correct oracle and registration numbers. I intentionally typed numbers in those cells to check if the CFs are working.
I deleted the columns with no data and several rows at the bottom in order to meet the limit of not more than 500 kb.

Attached Files

DATA1.ORACLE.xls (498.0 KB, 9 views)