Excel formulas from concatenated data

If, like me, you have been trying for ages to create formulas from concatenated data in Excel, then this is the post for you! For those of you who are already glazing over, perhaps skip this post :-)

I use spreadsheets a lot for descriptive analysis of survey data, managing student information, and for marking. It is with marking in mind that I wanted to use a concatenate function for creating a summary page in an Excel workbook. 

I have a tab (sheet) for each student, named with the student name so it auto-fills the student name field (see here for how to do that). That name is repeated on a summary page which is a VLookup so I don't have to fill in the student ID number (and here for how to do that). On that page I wanted to also show a copy of the mark for each student so I could see them all in one place, at a glance.

Concatenate seemed the logical function to compile the formula, but I could never get it to activate. I tried Shift and Enter, but that didn't work. I postponed following up on it. I would periodically search, but my search terms were obviously not accurate enough.

But then, late last year, I found a solution, thanks to tbhesswebber (2013), who posted that "a dynamic formula" was needed for "a template that we copy each month, so when I change the name of the month and I change the year, it will refer to a different folder and/or a different month within the folder". I wanted the formula to refer to a different name for each new marking sheet that I created. Same process, different object.

The solution from tbhesswebber got quite complicated, but my takeaway was that I needed to use "=INDIRECT()" with my concatenate formula nested inside (2013). The concatenate function was: (a) the open quote mark to signify that this was a named tab in the workbook, "'"; (b) the student name of the tab in "Student's Full Name", B2; (d) the close quote mark to close the named tab, "'"; (d) and the sum cell in each tab, or F$1. Then all I needed to do was to enclose this in the INDIRECT element. My formula was:

=INDIRECT(CONCATENATE("'",B2,"'",F$1))

Thank you very much, tbhesswebber!

Sam

Reference:

tbhesswebber. (3 February 2013). Answer to Can I create a formula using concatenate?. https://www.excelforum.com/excel-formulas-and-functions/592520-can-i-create-a-formula-using-concatenate.html