Use "INDIRECT" Function to reference sheet

Hello, I would like to use a formula to reference a sheet and corresponding cell within that sheet. For example, in the formula below, I'd like to instead of spelling out "123" (assuming that is the sheet name) I would like to have "123" in cell A1 and use A1 in the formula. Any help is greatly appreciated, thanks!

=SUM('123'!$O$3:INDEX('123'!$O$3:$O$75,MATCH(B2,'123'!$H$3:$H$75,0)))

Originally Posted by BoredWorker

first,
'123'!$O$3 is wrong, unless the sheet name have blank like 'Sheet 1'!A1, if not, it without ['], 123!A3

Second,
=SUM('123'!$O$3:INDEX('123'!$O$3:$O$75,MATCH(B2,'123'!$H$3:$H$75,0)))
if it only sum for two number, should use comma[,] istead of :

third,
bare in mind, Indirect, is not work when it refer to other closed workbook.
Here the formula,

=SUM(INDIRECT(A1&"!$O$3"),INDEX(INDIRECT(A1&"!$O$3:$O$75"),MATCH(B2,INDIRECT(A1&"!$H$3:$H$75"),0)))

This worked out great, thanks!

As far as your other points, "123" was just a generic name I put in, I believe you are correct it was Sheet 1 prior.
The sum is for a column, so I went with the colon

Appreciate the great feedback and sorry for the confusion.