Можно ли (наверняка можно, но не знаю как) использовать в формуле имя листа, в ячейке которого эта формула записана, как строковое значение в операции сравнения?

Не знаю способа сделать это, не прибегая к макросам, кто знает, поделитесь!

С использованием макросов можно сделать так.
1. Создать в книге модуль с произвольным именем, например, Module1.
2. Создать в нём такую функцию:

Public Function GetWorksheetName(r As Range) As String

    GetWorksheetName = r.Worksheet.Name

End Function

3. В ячейке, в которой требуется узнать имя листа, использовать вызов вышеупомянутой функции. Например, так:
=GetWorksheetName(A1)
Ячейка с такой формулой будет содержать имя листа, на котором она находится.