You are here:

Excel/School Reporting Spreadsheet


Hi Tom,
I'm a school teacher and looking to save time.
I was wondering if it's posssible to build a spread sheet with reporting data that can match and replace certain data.

I'm hoping to have a list of students and their genders and then comments for each subject depending on grade.

So for example if Dylan got a B in SOSE I was hoping to generate this comment.....

{Name} has shown deep understanding of how environments. He/She is able to accurately use the conventions of mapping.

But with his name and gender in the correct place.

Is this possible? If so, is it extremely intensive to set up the rules?



Say on a separate sheet named Comments you have

A2:  =B2&"_"&C2
C2:  B
D2:  {Name} has shown deep understanding of how environments. {HE} is able to accurately use the conventions of mapping.

So on your report sheet, say

F6: Dylan
G6: Male
I6: B

J6: =SUBSTITUTE(SUBSTITUTE(VLOOKUP(H6&"_"&I6,Comments!$A:$D,4,FALSE),"{Name}",F6),"{HE}",IF(G6="Male","He","She"))

when I set up my sheets as I describe, then the formula in I6 returns

Dylan has shown deep understanding of how environments. He is able to accurately use the conventions of mapping.

In Comments, column A produces a unique identifier such as SOSE_B

I use the values in H6 and I6 to build this identifier to be looked up in Comments column A
I6: B
H6&"_"&I6  produces  SOSE_B

vlookup looks that value up in the sheet comments in column A.  The 4 as the 3rd argument of Vlookup says to return the value from the 4th column of the Lookup range in the same row as a match is found for the lookup value.  In this case, the match is made in row 2 and the comment you showed is returned.  This returned string is processed by the second Substitute function to replace the case sensitive string {Name} with the name in F6.  This adjusted returned string is processed by the first Substitute function and it replaces the case sensitive substring "{HE}" with the results of the if function  =if(G6="Male","He","She")

so the final returned value is as I show.  

So you would just need to build your comments sheet to contain the values you want similar to what I have shown.  You would have each row in that sheet identified by the unique combination of Subject and Grade.

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.