Say you have multiple months for which you need to calculate data. Each months' data is on a separate sheet.
INDIRECT() allows you to reference a cell (containing the sheet name) which then points you to the correct sheet. Just set it up once and you can drag the formula without any manual formula changes. I used it nested in a COUNTIFS() statement, as follows:
=COUNTIFS(INDIRECT("'"&I$2&"'!"&"$K:$K"),">"&$H8,INDIRECT("'"&I$2&"'!"&"$K:$K"),"<"&$G10)
Learn more about it here: http://www.contextures.com/xlFunctions05.html
