I really don't like to admit that "No, you can't" is an acceptable answer, but sometimes it is. Since the end result of the project was Excel spreadsheets, I had to move some of the computation to Excel. A bit kludgy, but in this particular case there was no viable option. So, to sum up my process for some future Google searcher (Hi, Google searcher!):
- Create a list of Members, with Name as the one field, and Title as another, like "Dr.", "Mr.", "Professor", etc. NOTE: The default Title field is actually the full name of the member, so don't confuse it with my Title field.
- Create a calculated field NameAndTitle that concatenates Name and Title fields (mine is actually more complex, involving dates and such).
- Create a Roster list with a Position field. Add a lookup field to Members that includes Name and Title, the necessary fields for the calculated field formula.
- Populate the Roster list with Members and Positions and export it to Excel, including all the fields.
- In Excel, create a NameAndTitle calculated field, using the same formula from the Members list.
- In Excel, hide all fields except Position and NameAndTitle. Distribute this spreadsheet (or copy just those columns to a new spreadsheet).
I appreciate Terri's suggestion, in SOME cases you should be able to redesign your schema. In my particular case, Title and Position were not related, and a Member could have more than one Position in more than one Roster, so combining into one list wasn't an option. But valid advice for most situations.
UPDATE: One final note, another option was to copy the calculated field to a text field in Members, and reference that copied field. But then, I would have to manually update that calculated field every time a Member's Name or Title changed. In my solution, since I have to reproduce the Excel spreadsheets on a regular basis, I can just copy a block of data to the spreadsheet, preserving the calculated column in Excel.