How to reference worksheet with space in name

This looks like your previous problem with the added twist that the sheet name consists of several words. Excel handles those references by surrounding the sheet name in single quotes (apostrophes). So the reference string in cell A1 would be:

='Sheet Space'!E8

Converting that to an INDIRECT reference you can copy down the column would look like this:

=INDIRECT("'"&$C$1&"'!E"&ROW()+7)

You were on the right track with what you needed to do. With INDIRECT, you can build the cell reference with a combination of literal strings, cell references, and formulas. So you just build what you need as a string.

BTW, those single quotes don't hurt anything if the sheet name doesn't contain spaces. So this version of the formula can be used with any kind of sheet name in C1.